Missing Id from table shown in two columns

Viewed 40

I want to get the list of ID-numbers that is missing in a table.

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from MYTABLE)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN MYTABLE on MYTABLE.id = Missing.missnum
WHERE MYTABLE.id is NULL
OPTION (MAXRECURSION 0); 

List of missing ID-numbers:

id
2
3
4
5
7
8
9

But I don't want the list to show all individual numbers - only a list with two columns like

My output should be:

from to
2 5
7 9

Now I can see that my question was not clear enough.

I have a long list of thousands of rows of numbers;

2,3,4 .... 36 401,402,403 ... 807 1234,1235 ... 1240 etc.

I would like to present them as;

from to
2 36
401 807
1234 1240
1 Answers

Use the table join to itself

SELECT 
        t1.id AS [from],
        t2.id AS [to]
     FROM test t1
     JOIN
     test t2 ON t1.id < t2.id
WHERE t2.id - t1.id = 2 

demo in db<>fiddle

Related