Home > database >  Get first row that is null in Oracle DB table
Get first row that is null in Oracle DB table

Time:09-13

I have the following table

rownum ID date owner
1 1 09/01/2022 null
2 1 09/02/2022 null
3 1 09/03/2022 Joe
4 1 09/04/2022 null
5 1 09/05/2022 Jack
6 2 09/01/2022 null
7 2 09/02/2022 John
8 2 09/02/2022 John
9 2 09/02/2022 John

For every ID, I want to select the first occurrence of null that eventually results in a non-null value. So rows 1, 4, and 6.

(Note that rownum is not a column; I just added it here for illustration only)

Query output:

ID date owner
1 09/01/2022 null
1 09/04/2022 null
2 09/01/2022 null

What is the best way to go about it on an Oracle 11g DB?

CodePudding user response:

We can phrase this as a gaps and islands problem, with each island being a contiguous group of NULL owner records. From each island, we can return the earliest date.

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY "date") rn1,
                ROW_NUMBER() OVER (PARTITION BY ID, owner ORDER BY "date") rn2
    FROM yourTable t
)

SELECT ID, MIN("date") AS "date", NULL AS owner
FROM cte
WHERE owner IS NULL
GROUP BY ID, rn1 - rn2
ORDER BY ID, "date";

screen capture from demo link below

Demo

  • Related