By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop
table if exists #temp
select
* into #temp
from
(
values
('id100', 'status1', 1),
('id100', 'status2', 2),
('id100', 'status1', 3),
('id100', 'status0', 4),
('id100', 'status2', 5),
('id100', 'status2', 6),
('id100', 'status1', 7),
('id100', 'status1', 8),
('id100', 'status2', 9),
('id101', 'status1', 10),
('id101', 'status2', 11)
) t(id, status, rowNum)
11 rows affected
select *, CASE WHEN status='status2' then (select max(rowNum) from #temp tmp
where tmp.rowNum<t.rowNum and tmp.status='status1') end value
from #temp t
order by rowNum
| id | status | rowNum | value |
|---|---|---|---|
| id100 | status1 | 1 | null |
| id100 | status2 | 2 | 1 |
| id100 | status1 | 3 | null |
| id100 | status0 | 4 | null |
| id100 | status2 | 5 | 3 |
| id100 | status2 | 6 | 3 |
| id100 | status1 | 7 | null |
| id100 | status1 | 8 | null |
| id100 | status2 | 9 | 8 |
| id101 | status1 | 10 | null |
| id101 | status2 | 11 | 10 |