Split intervals of numbers into groups

Question

I have the sequence of numbers below and I want to make a new group of numbers every time the following number has a difference of more than one unit from the previous value, i.e. breaking the continuous interval

Table T

value
1 
2
3
5
6
7
15
16
17
18

Groups to be formed of continuous interval:

min max
1   3
5   7
15  18

I am trying to number the rows using dense_rank() function, like the example below, then i will be able to group by rankNumber and get the MIN(value) and MAX(value), but i am not finding a pattern to use in the PARTITION BY clause of this function

value rankNumber
1     1
2     1
3     1
5     2
6     2
7     2
15    3
16    3
17    3
18    3

    WITH T2 AS
    (
     SELECT value, LEAD(value) OVER(ORDER BY value) as nextValue
     FROM T 
    )

    SELECT value, DENSE_RANK() 
     OVER(PARTITION BY CASE WHEN nextValue - value > 1 THEN 1 ELSE 0 END ORDER BY value)
    FROM T2

Code to create table:

CREATE TABLE t(
    value   INT
);
INSERT INTO t VALUES
    (1), (2), (3), (5), (6), (7), (15), (16), (17), (18);

Current output using the query above:

value rankNumber
1     1
2     2
3     1
5     3
6     4
7     2
15    5
16    6
17    7
18    8

Answer 1

You need to think out some way to turn the sequences into the corresponding groups. I've just learnt this trick from another user right in here. By using the ROW_NUMBER which runs through all the records, you can calculate the group key by subtracting the value on the same record from that row number. If the values are consecutive, there would be no change in the subtraction result (hence the same group key produced). Otherwise the group key will be jumped to the next (lesser) value. Each time of jumping, the group key will be lesser.

Here is the query:

select min(value) min, max(value) max
from (select value, ROW_NUMBER() over (order by value) - value as [key] 
      from t) v
group by [key]
order by min