In pseudo code, group_index is defined as: case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?
If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right? Something like this? WITH vals (i,v) AS (VALUES (0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1)), grouped_vals AS (SELECT *, case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i END AS group_index FROM vals ) select *, row_number() OVER (PARTITION BY group_index ORDER BY i) from grouped_vals where v = 0;
