Home > Software engineering >  PostreSQL Find nearest above row without a specific value
PostreSQL Find nearest above row without a specific value

Time:12-10

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

DB<>Fiddle

  • Related