Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# find gap in number sequence

Posted on 2008-10-13
Medium Priority
929 Views
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
0
Question by:dgrafx
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 5

Accepted Solution

ID: 22707406
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

LVL 60

Expert Comment

ID: 22707418
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

LVL 60

Expert Comment

ID: 22707464
Check out the vw_nums code in this following post by BrandonGalderisi:
http:Q_23786715.html#a22639391
0

LVL 5

Expert Comment

ID: 22707472
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

LVL 60

Assisted Solution

Kevin Cross earned 1000 total points
ID: 22707477
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

LVL 60

Expert Comment

ID: 22707478
True!! :)
0

LVL 39

Expert Comment

ID: 22707705
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)

the great thing is that it's not a table.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month6 days, 11 hours left to enroll