TSQL Window Function to split dataset into groups [duplicate]-CodePudding

There must be a simple TSQL solution to this. I just don't know what it is.

I have a table that looks like this:

Col1	Col2
1	0
2	0
3	`1`
4	`1`
5	0
6	`1`
7	`1`
8	`1`
9	0
10	`1`

SELECT Col1, Col2 FROM dbo.Table ORDER BY Col1

As you can see, when you order the table by Col1 it produces groups of rows in Col2 of either 1s or 0s.

All I want to do is label the groups consecutively, 1,2,3, and so on, to produce the following:

Col1	Col2	NewColumn
1	0	1
2	0	1
3	`1`	`2`
4	`1`	`2`
5	0	3
6	`1`	`4`
7	`1`	`4`
8	`1`	`4`
9	0	5
10	`1`	`6`

There must be a Windows function or something that does this. I've poured over Itzik Ben-Gan's book on it, googled it, and I just can't find a solution. I imagine it has something to do with ROW_NUMBER but I can't make it work. Any help would be greatly appreciated.

If you'd like to play around with it, here is how you can produce the table:

    SELECT 
        Col1,Col2
    FROM
        (
            SELECT 1 Col1, 0 Col2
            UNION SELECT 2,0
            UNION SELECT 3,1
            UNION SELECT 4,1
            UNION SELECT 5,0
            UNION SELECT 6,1
            UNION SELECT 7,1
            UNION SELECT 8,1
            UNION SELECT 9,0
            UNION SELECT 10,1
        ) a
ORDER BY Col1

CodePudding user response：

You can use lag() and then a cumulative sum:

select col1, col2,
       sum(case when prev_col2 = col2 then 0 else 1 end) over (order by col1) as grouping
from (select t.*,
             lag(col2) over (order by col1) as prev_col2
      from t
     ) t;