devxlogo

Find Missing Numbers in a Sequence

Find Missing Numbers in a Sequence

There are several cases where you may write algorithms only to find missing numbers in a sequence. There is an efficient way to do this. Let’s say you have a table like this:

 Mynum-----1346791016

Here is the script to create the table and insert sample data.

 --DROP TABLE #testCREATE TABLE #test(mynum INT)INSERT INTO #test VALUES (1)INSERT INTO #test VALUES (3)INSERT INTO #test VALUES (4)INSERT INTO #test VALUES (6)INSERT INTO #test VALUES (7)INSERT INTO #test VALUES (9)INSERT INTO #test VALUES (10)INSERT INTO #test VALUES (16)--SELECT * FROM #testSELECT A.mynum+1 AS 'MissingFROM', MIN(B.mynum)-1 AS 'TO'FROM #test A, #test B	WHERE A.mynum 

devx-admin

Share the Post: