SQL Puzzle | The Maximum Consecutive Numbers puzzle

In this puzzle you need to find numbers with highest count of consecutive integers in a single column table. Like in below example-

Longest sequence of consecutive numbers in table could be 5,6,7,8 & 4 is the length of the longest sequence.

Other sequences are – 1,2,3 and the length is 3
– 12 and the length is 1

Hence we require output as starting Number of the sequence, ending number in the sequence and the length of the sequence.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE t
(
 id INT
)

insert into t values(7)
insert into t values(1)
insert into t values(2)
insert into t values(5)
insert into t values(6)
insert into t values(8)
insert into t values(12)
insert into t values(3)
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1

--

;WITH CTE1 AS
( 
       SELECT id , ROW_NUMBER() OVER (ORDER BY id) Rnk FROM t 
)
,CTE2 AS
(
       SELECT *,  CASE WHEN Id-1 = LAG(Id) OVER(ORDER BY rnk) THEN 0 ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) OVER(ORDER BY rnk) Grouper FROM CTE2 c2
)
SELECT TOP 1 * FROM 
(
	SELECT MIN(Id) MinId, MAX(Id) MaxId , COUNT(*) Counts FROM CTE3 GROUP BY Grouper
)r
ORDER BY Counts DESC

--

Solution 2

--

;WITH CTE1 AS
( 
       SELECT id , id - ROW_NUMBER() OVER (ORDER BY id) Rnk FROM t 
)
SELECT TOP 1 *
FROM
(
	SELECT MIN(Id) MinId, MAX(Id) MaxId ,COUNT(*) Cnts FROM CTE1
	GROUP BY Rnk
)t
ORDER BY Cnts DESC

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com