Solved

Find all not existsing rows in a range?

Posted on 2008-10-14
11
266 Views
Last Modified: 2011-09-20
Hi,

I have a table with the following columns: id, number.

id is an ID of the row and number is a number between 0 to 10.

I have the following rows:
ID    number
1     6
2     2
3     4
4     9
5     1

it is possible to create a query that returns the missing numbers in this range?

So it will return
3, 5, 7, 8

?

Thanks!
0
Comment
Question by:DotanSimha
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22713342
if it's always 0-10...
select * from 
(select 0 tNum
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10)a
where tNum not in (
select distinct number from YourTable)

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 400 total points
ID: 22713421
I agree with Brandon.  

Judging from "So it will return 3, 5, 7, 8" I am re-reading the statement "a number between 0 to 10" to mean this is not inclusive.  If that is the case, then Brandon's answer should still work just change first value to 1 and remove the extra select 1 and select 10 statements.

Again this is Brandon's answer rewritten:
select * from 
(select 1 tNum
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9)a
where tNum not in (
select distinct number from YourTable)

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22713528
Why remove 0?  The asker did say "id is an ID of the row and number is a number between 0 to 10"

0
1
2
3
4
5
6
7
8
9
10
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:DotanSimha
ID: 22713539
It's not always 0-10... it could be diffrent each time...
another solution?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22713694
I removed it Brandon because the displayed results excluded 0 and 10 even though they were not in the list; therefore, I was asking if that meant that between was representing numbers that fell within a range not including the end points.

DotanSimha, is there any consistency to the numbers?  

Are you wanting, given a list of numbers find the gaps between the lowest number and the highest?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22713722
"representing numbers that fell within a range not including the end points."

Got it!


"Are you wanting, given a list of numbers find the gaps between the lowest number and the highest?"
vw_nums? :)


What db engine and version are you using?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 22713747
Take a look here - http:Q_23811185.html#a22707705

You can read the posts above it for details on vw_num (also a creation of Brandon's).

The final solution would look something similar to this (will make sense after reading the linked question details):
SELECT n
FROM vw_Nums v LEFT JOIN TableName t
ON v.n = t.number
WHERE t.number IS NULL
AND n BETWEEN (select min(number) from tablename) AND (select max(number) from tablename)

Open in new window

0
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 100 total points
ID: 22713756
if you can use multiple sql statements, you may use a temp table to hold the range of values

select top 10 identity(int, 1,1) num into #t3 from sysobjects

select * from #t3 where num not in (
select distinct number from YourTable)

drop table #t

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22713763
Sorry, Brandon.  Didn't see your post, why don't you finish it out -- don't want to take credit for your genius.  :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22713804
http:#22713763

If I wanted credit for every thing I ever created, I wouldn't be able to post it here now would I?

:)

Besides, I think you cover it well in http:#22713747.

But it's all depend upon this question I asked previously. "What db engine and version are you using?"
0
 

Author Comment

by:DotanSimha
ID: 22713818
Thank you all!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …

809 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