Get first row that is null in Oracle DB table

Question

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?

Tim Biegeleisen · Answer

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";

Get first row that is null in Oracle DB table

1 Answers

Demo