Solved

find gap in number sequence

Posted on 2008-10-13
7
925 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
[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
  • Learn & ask questions
  • 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 60

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 60

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 60

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 60

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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