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