CODEWITHSUNDEEP
sql-servernullgroupinggaps-and-islands
user1094183
user1094183

Reputation: 175

SQL Server update rows without nulls ordered by consecutive numbers and nulls

I have query returning few rows. There is column with consecutive numbers and nulls in it.

For example, it has values from 1-10 then 5 nulls, then from 16-30 and then 10 nulls, then from 41-45 and so on.

I need to update that column or create another column to create groupId for consecutive columns.

Meaning as per above example, for rows 1-10, groupID can be 1. Then for 5 nulls nothing and then from 16-30 groupId can be 2. Then for 10 nulls nothing. Then from 41-45 groupId can be 3 and so on.

Please let me know

Upvotes: 0

Views: 68

Answers (2)

Squirrel
Squirrel

Reputation: 24763

Typical island & gap solution

select  col, grp = dense_rank() over (order by grp)
from    
(
    select  col, grp = col - dense_rank() over (order by col)
    from    yourtable
) d

Upvotes: 1

TomC
TomC

Reputation: 2814

This was a fun one. Here is the solution with a simple table that contains just integers, but with gaps.

create table n(v int)
insert n values (1),(2),(3),(5),(6),(7),(9),(10)

select n.*, g.group_no
from n
join (
select row_number() over (order by low.v) group_no, low.v as low, min(high.v) as high
from n as low
join n as high on high.v>low.v 
    and not exists(select * from n h2 where h2.v=high.v+1)
where not exists(select * from n l2 where l2.v=low.v-1)
group by low.v
) g on g.low<=n.v and g.high>=n.v

Result:

v   group_no
1   1
2   1
3   1
5   2
6   2
7   2
9   3
10  3

Upvotes: 2

Related Questions