# 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
###### Who is Participating?

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

Chief 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

Chief Technology OfficerCommented:
Check out the vw_nums code in this following post by BrandonGalderisi:
http:Q_23786715.html#a22639391
0

Commented:
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

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
``````
0

Chief Technology OfficerCommented:
True!! :)
0

Commented:
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)