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