CODEWITHSUNDEEP
oracle-databasenotnull
Magic Mick
Magic Mick

Reputation: 1523

ORACLE find first non-null value from history table

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.

Upvotes: 0

Views: 3599

Answers (3)

Ashish Patil
Ashish Patil

Reputation: 4604

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;

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

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

Upvotes: 1

WW.
WW.

Reputation: 24291

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;

Upvotes: 1

Related Questions