Search code examples
t-sqlselectgroupingunique

TSQL Order/Sequence groups in a query

I have the following data...

uid groupid
1 0
2 0
3 1
4 1
5 1
6 1
7 0
8 0
9 2
10 2
11 2

I would like to uniquely order the groups so that I get....

uid groupid newGroupId
1 0 0
2 0 0
3 1 1
4 1 1
5 1 1
6 1 1
7 0 2
8 0 2
9 2 3
10 2 3
11 2 3

Can anyone help me do this in SQL (SQL Server)

Solution 
  • with cte as 
(
    select * from (values 
        (1  , 0),
        (2  , 0),
        (3  , 1),
        (4  , 1),
        (5  , 1),
        (6  , 1),
        (7  , 0),
        (8  , 0),
        (9  , 2),
        (10 , 2),
        (11 , 2))
    t([uid], [groupid])
),
cte2 as
(
    select 
        [uid], [groupid],
        newGroupId = 
            row_number() over(order by [uid]) 
            - 
            row_number() over(partition by [groupid] order by [uid])    
    from 
        cte
)
select
    [uid], [groupid],
    newGroupId = dense_rank() over(order by newGroupId) - 1
from
    cte2;