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 |