Solved

Find all not existsing rows in a range?

Posted on 2008-10-14
11
263 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:Kevin Cross
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you all!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
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.…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 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

8 Experts available now in Live!

Get 1:1 Help Now