Get consecutive numbers Range from SQL Server Table

Question

I am using SQL Server 2014. I have a Table having Column BookNo as datatype int. This column contains below data

|BookNo|
1
2
3
4
5
10
12
13
25
26
27
28

I want to the Consecutive Numbers Range in Sql query. From above data my output should be like

1 to 5
10 to 13
25 to 28

Any Help...

Answer 1

I think you can use a query like this:

SELECT BookNo, ISNULL(LEAD(prev) OVER (ORDER BY BookNo) , (SELECT MAX(BookNo) FROM yourTable)) As toCon
FROM
    (
    SELECT *, LAG(BookNo) OVER (ORDER BY BookNo) prev, BookNo - LAG(BookNo) OVER (ORDER BY BookNo) diff
    FROM yourTable) dt
WHERE 
    (ISNULL(diff, 0) <> 1);

[SQL Fiddle Demo]

Answer 2

Another solution based on Windowed Aggregate Functions which also runs on versions below SS2014 (and should perform better than the LAG/LEAD):

SELECT MIN(BookNo) AS BookNoFrom, MAX(BookNo) AS BookNoTo
FROM
  (
    SELECT BookNo, 
        BookNo - ROW_NUMBER() OVER (ORDER BY BookNo) AS dummy
    FROM yourTable
  ) dt
GROUP BY dummy

See Fiddle

The dummy calculation is based on the fact that both BookNo and ROW_NUMBER are sequential numbers, but there might be gaps in the BookNo. For consecutive BookNo the difference is always the same, when there's a gap it increases (the dummy value has no actual meaning, but it's the same value for consecutive rows).