Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

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
0
dgrafx
Asked:
dgrafx
  • 4
  • 2
2 Solutions
 
adlink_laCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 CrossChief 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now