By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT Id,
Special,
MAX(CASE WHEN Special = 1 THEN Id END) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING)
FROM (values
(1, 0),
(2, 0),
(3, 1),
(4, 0),
(5, 0),
(6, 0),
(7, 1),
(8, 0),
(9, 0)
) YourTable(Id,Special)
ORDER BY Id
| Id | Special | (No column name) |
|---|---|---|
| 1 | 0 | null |
| 2 | 0 | null |
| 3 | 1 | 3 |
| 4 | 0 | 3 |
| 5 | 0 | 3 |
| 6 | 0 | 3 |
| 7 | 1 | 7 |
| 8 | 0 | 7 |
| 9 | 0 | 7 |
Warning: Null value is eliminated by an aggregate or other SET operation.