0

I have a history table where I want to return ONLY the first STATUS value which is not null (if any). I haven't got much ORACLE experience and I have searched for this but couldn't find anything I need.

EDIT Example of my table below.

ITEM   NAME   STATUS
Item1  Test1  Null
Item1  Test2  Null
Item1  Test3  Approved
Item2  Test1  Null
Item2  Test2  Approved

In the example above, I would ONLY like to return the following rows.

Item1 Test3 Approved
Item2 Test2 Approved

Thanks in advance.

Magic Mick
  • 1,475
  • 1
  • 21
  • 32

3 Answers3

1

You haven't specified what you mean by "first" (ordered how?), so this will do:

SELECT *
FROM t
WHERE status IS NOT NULL
AND ROWNUM = 1;
WW.
  • 23,793
  • 13
  • 94
  • 121
  • Apologies, I have edited my initial question. I would like the first `STATUS` value that is not null to be returned for all items in the table. I have also edited my example. – Magic Mick Jul 17 '15 at 03:48
1

I think that you mean: For each value of item, order the rows with non-NULL status by name and take the first one. If that's correct:

SELECT * FROM (
  SELECT
    item, name, status,
    row_number() OVER (PARTITION BY item ORDER BY name) AS rn
  FROM t
  WHERE status is NOT NULL
)
WHERE rn = 1
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
0

By referring @WW's answer, If you wanted to return all items then only modify following (i.e. remove rownum condition) 

SELECT *
FROM t
WHERE status IS NOT NULL;
Ashish Patil
  • 4,428
  • 1
  • 15
  • 36