Let's say I have the following table:
| id | column B |
|---|---|
| 1 | value A |
| 2 | value B |
| 3 | value D |
| 4 | value C |
| 5 | value D |
| 6 | value D |
| 7 | value D |
| 8 | value E |
| 9 | value F |
| 10 | value D |
For each row with value D, I need to find the nearest above row with a value different than D. So what I need is:
| id | nearest row id |
|---|---|
| 1 | null |
| 2 | null |
| 3 | 2 |
| 4 | null |
| 5 | 4 |
| 6 | 4 |
| 7 | 4 |
| 8 | null |
| 9 | null |
| 10 | 9 |
How can I achieve this in PostreSQL?
CodePudding user response：
Ideally this should be done via LAG() IGNORE NULLS (...) but PostgreSQL does not support it. The workaround is to think that every value other than value d starts a group, then copy the desired value across the group:
with cte1 as (
select *, case when b = 'value d' then null else id end as grp_id
from t
), cte2 as (
select *, sum(case when grp_id is not null then 1 end) over (order by id) as grp_num
from cte1
)
select *, case when b = 'value d' then max(grp_id) over (partition by grp_num) end as nearest_row_id
from cte2