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