By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #tmp
(
a CHAR(1)
, b INT
);
INSERT INTO #tmp (a
, b)
VALUES ('a', 1)
, ('b', 2)
, ('z', 3)
, ('c', 4)
, ('z', 5)
, ('z', 6)
, ('d', 7);
7 rows affected
select t.*,
coalesce(sum(case when a = 'z' then 1 else 0 end) over
(order by b
rows between unbounded preceding and 1 preceding), 0
) as grp_desc
from #tmp t
order by b;
| a | b | grp_desc |
|---|---|---|
| a | 1 | 0 |
| b | 2 | 0 |
| z | 3 | 0 |
| c | 4 | 1 |
| z | 5 | 1 |
| z | 6 | 2 |
| d | 7 | 3 |