TSQL Order/Sequence groups in a query-CodePudding

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)

CodePudding user response：

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;

CodePudding user response：

Slightly different approach than Vadium, but another option to consider:

;WITH CTE_MAIN 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])
),
CTE_2 AS
(
    SELECT uid, groupid, LAG(groupid,1,-1) OVER(ORDER BY uid) LastGroupID
    FROM CTE_MAIN
)
SELECT uid, groupid, SUM(CASE WHEN GroupID <> LastGroupID THEN 1 ELSE 0 END) OVER(ORDER BY uid) - 1 NewGroup
FROM CTE_2