CODEWITHSUNDEEP
sqlsql-servert-sql
Sifeddine
Sifeddine

Reputation: 11

SQL Group by sequential range

I need some SQL code to have a result like this (grouped by sequential range). I use SQL server 2012

my table : 

013100      

013101

013102

013108

013203

013204

013242

013244

013243

needed result : 

013100           3

013108           1

013203           2

013242           3

Upvotes: 1

Views: 198

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a difference of the value and a row number, for this version of gaps-and-islands:

select min(code), count(*)
from (select t.*,
             cast(code as int) as code_num,
             row_number() over (order by code) as seqnum
      from t
     ) t
group by (code_num - seqnum)
order by min(code);

Upvotes: 2

Related Questions