CODEWITHSUNDEEP
sqlsql-server
user9773216
user9773216

Reputation:

Find max uninterrupted interval

How I can find max uninterrupted interval in column?

Example

ID    Result
1       1
2       2
3       3
4       4
5       5
6       6
10
11
12

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use row_number(). Here is a simple way to get the first and lsat values:

select top (1) with ties min(id), max(id)
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t
group by (id - seqnum)
order by count(*) desc;

To get the actual original rows requires another level of window functions:

select top (1) with ties
from (select t.*, count(*) over (partition by id - seqnum) as cnt
      from (select t.*, row_number() over (order by id) as seqnum
            from t
           ) t
     ) t
order by cnt desc, id;

Upvotes: 3

Related Questions