Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Sep 12, 2002

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
-----
1
3
4
6
7
9
10
16

Here is the script to create the table and insert sample data.
 
--DROP TABLE #test
CREATE 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 #test

SELECT A.mynum+1 AS 'MissingFROM', MIN(B.mynum)-1 AS 'TO'
FROM #test A, #test B
	WHERE A.mynum < B.mynum
	GROUP BY A.mynum
	HAVING A.mynum + 1 < MIN(B.mynum)
	ORDER BY 1
Harinatha Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date