Solved

find gap in number sequence

Posted on 2008-10-13
7
922 Views
Last Modified: 2012-05-05
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
Comment
Question by:dgrafx
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
adlink_la earned 250 total points
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 59

Expert Comment

by:Kevin Cross
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 59

Expert Comment

by:Kevin Cross
ID: 22707464
Check out the vw_nums code in this following post by BrandonGalderisi:
http:Q_23786715.html#a22639391
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Expert Comment

by:adlink_la
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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 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

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22707478
True!! :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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)


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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now