Solved

Find all not existsing rows in a range?

Posted on 2008-10-14
11
268 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
[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
  • 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 60

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

 

Author Comment

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

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 60

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 60

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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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