Assign Unique Group Id To Sets of Rows with Same Column Value Separated by Other value

I have some data that looks like this:

uid radius
1   10
2   10
3   10
4   2
5   4
6   10
7   10
8   10

What I want is for each group which has the same radius value to have its own unique id, for example:

uid radius GroupdId
1   10     1
2   10     1
3   10     1
4   2      2
5   4      3
6   10     4
7   10     4
8   10     4

What I don’t want is the second group with radius 10 to have the same groupid as the first group (not 1).

I’m working on SQL Server but the solution should be the same across all databases.

(I’ve done this before, but for the life of me, I can’t remember how I did it.)

Answer

Try this:

with t as
(
    select
        uid,
        radius,
        lag(radius,1) over (order by uid) as prev_rad
    from
        radtable
)

select
    uid,
    radius,
    sum
    (
        case when radius = coalesce(prev_rad,radius) then 0 else 1 end
    )  
    over 
    (
        order by uid
    ) + 1 as GroupID
from 
    t