Solved

# Find all not existsing rows in a range?

Posted on 2008-10-14
263 Views
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
Question by:DotanSimha
• 4
• 4
• 2
• +1

LVL 39

Expert Comment

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)
``````
0

LVL 59

Accepted Solution

Kevin Cross earned 400 total points
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)
``````
0

LVL 39

Expert Comment

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

It's not always 0-10... it could be diffrent each time...
another solution?
0

LVL 59

Expert Comment

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

"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

Kevin Cross earned 400 total points
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)
``````
0

LVL 9

Assisted Solution

jamesgu earned 100 total points
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

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

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

Thank you all!
0

## Featured Post

### Suggested Solutions

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: …