find gap in number sequence

hello
for ex you have rows in a table and a columns value is:
1
2
3
5
6
you see how 4 is missing
I'm looking for a quick method to find just the first lowest missing number or gap

actually - lets say 1 is missing and there are other rows in the table
then 1 is the first gap
so I'm in need of that also

thanks
LVL 25
dgrafxAsked:
Who is Participating?
 
adlink_laConnect With a Mentor Commented:
You need to outer join your table to a table (or inline query) that has all of the numbers in it.  Then look for any records in your table that have nulls.
0
 
Kevin CrossChief Technology OfficerCommented:
I would agree with adlink_la, if you don't already have a numbers table in your database I saw a good method of creating one from Brandon using common table expression. If I find I will post.
0
 
Kevin CrossChief Technology OfficerCommented:
Check out the vw_nums code in this following post by BrandonGalderisi:
http:Q_23786715.html#a22639391
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
adlink_laCommented:
Yup.  That is a very cool little bit of code.  One thing though, you probably don't need 4 billion records.  That table might be a little large. =B
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Combine that with what adlink_la is saying and you get solution like this:
SELECT MIN(n)
FROM vw_Nums v LEFT JOIN TableName t
ON v.n = t.NumberColumn
WHERE t.NumberColumn IS NULL

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
True!! :)
0
 
BrandonGalderisiCommented:
The other thing you could do (if you are looking for gaps) is limit n to being < max.


SELECT MIN(n)
FROM vw_Nums v LEFT JOIN TableName t
ON v.n = t.NumberColumn
WHERE t.NumberColumn IS NULL
and n < (select max(t.numbercolumn) from tablename)


adlink_la "that table might be a little large"
the great thing is that it's not a table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.