Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
(id int, b varchar(10));
insert into t(id, b) values
(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');
10 rows affected

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


id
b
prev_id
grp_num
nearest_row_id


1
value a
1
1
null

2
value b
2
2
null

3
value d
null
2
2

4
value c
4
3
null

5
value d
null
3
4

6
value d
null
3
4

7
value d
null
3
4

8
value e
8
4
null

9
value f
9
5
null

10
value d
null
5
9

id	b	prev_id	grp_num	nearest_row_id
1	value a	1	1	null
2	value b	2	2	null
3	value d	null	2	2
4	value c	4	3	null
5	value d	null	3	4
6	value d	null	3	4
7	value d	null	3	4
8	value e	8	4	null
9	value f	9	5	null
10	value d	null	5	9