Find missing numbers in a sequence in MS SQL

Question

Say i have a table with an integer column Id. I need to find the missing numbers in a sequence with a maximum returned amount.

If the table is empty and i'm asking for 10, it should return the numbers 1-10.
If the table has 1-5 and i'm asking for 10, it should return the numbers 6,7,8,9,10,11,12,13,14,15.
If the table has 1,2,4,6,9 and im asking for 10, it should return the numbers 3,5,7,8,10,11,12,13,14,15

How can i achive this in one single query using MS SQL?

Thanks in advance!

Possible duplicate of [SQL: find missing IDs in a table](https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table) — Piro, Mar 27 '18 at 10:48

DineshDB · Accepted Answer · 2018-03-27T11:34:15.970

5

Try this:

If you need to get more numbers, just increase the WHERE Number<=100.

DECLARE @Tab1 TABLE (ID INT)

INSERT INTO @Tab1 VALUES(1)
INSERT INTO @Tab1 VALUES(3)
INSERT INTO @Tab1 VALUES(5)
INSERT INTO @Tab1 VALUES(7)
INSERT INTO @Tab1 VALUES(9)

;WITH CTE AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1 FROM CTE
    WHERE Number<=100
)
SELECT TOP 5 *
FROM CTE
WHERE Number NOT IN(SELECT ID FROM @Tab1)
ORDER BY Number
OPTION (maxrecursion 0);

Existing values:

Number
1
3
5
7
9

OutPut:

Number
2
4
6
8
10

Hope this helps you.

edited Mar 27 '18 at 11:34

answered Mar 27 '18 at 10:57

DineshDB

5,998
7
33
49

Looks promising.. What does this do: ;WITH CTE AS ( SELECT 1 AS Number UNION ALL SELECT Number + 1 FROM CTE WHERE Number<=100 ) – Lasse O Mar 27 '18 at 11:02
@LasseO, This is for generating numbers in sequence. Using that sequence we eliminate the existing numbers from the table. – DineshDB Mar 27 '18 at 11:03
This is OK, but keep in mind 2 things. If you request many rows, you might need to add `OPTION (MAXRECURSION X)` at the end, and I would suggest changing the `NOT IN` for a `NOT EXISTS` if your table is big or it has `NULL` IDs. – EzLo Mar 27 '18 at 11:09
I'm using this to get which file chunk id's has not yet been uploaded. There can be thousands of chunks though. I believe this only supports op to 100? – Lasse O Mar 27 '18 at 11:29
@LasseO, That's what I clearly said you in the answer. If you need more numbers, you increase the value in the `where` condition inside the `CTE`. – DineshDB Mar 27 '18 at 11:31
Yeah, but i get this error: "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – Lasse O Mar 27 '18 at 11:32
@EzequielLópezPetrucci, how would that look? – Lasse O Mar 27 '18 at 11:33
@LasseO Try my Updated answer. – DineshDB Mar 27 '18 at 11:34

score 1 · Answer 2 · answered Mar 27 '18 at 11:39

This should work
There are also a system table with numbers

declare @T table (i int primary key);
insert into @T values (1), (2), (4), (6), (9); 
declare @count int = 10;
declare @size int = (select count(*) from @T);
with cte as
( select 1 as num
  union all 
  select num + 1 
  from cte
  where num + 1 <= (@count + @size)
)
select top (@count) cte.num 
from cte 
left join @T t 
  on t.i = cte.num 
where t.i is null 
order by cte.num 
option ( MaxRecursion 0 );

Find missing numbers in a sequence in MS SQL

2 Answers2