CODEWITHSUNDEEP
sqlsql-servert-sql
Simon Greener
Simon Greener

Reputation: 425

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.)

Upvotes: 1

Views: 964

Answers (1)

Nabav
Nabav

Reputation: 273

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

Upvotes: 5

Related Questions