PostreSQL Find nearest above row without a specific value

December 10, 2021

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?

>Solution :

Ideally this should be done via LAG() IGNORE NULLS (...) but PostgreSQL does not support it. So you need to use this trick to copy a value downwards inside a window:

with cte1 as (
    select *, case when b = 'value d' then null else id end as prev_id
    from t
), cte2 as (
    select *, sum(case when prev_id is not null then 1 end) over (order by id) as grp_num
    from cte1
)
select *, case when b = 'value d' then max(prev_id) over (partition by grp_num) end as nearest_row_id
from cte2

DB<>Fiddle