# Find all not existsing rows in a range?

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!
###### Who is Participating?

Chief Technology OfficerCommented:
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

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

Commented:
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 Commented:
It's not always 0-10... it could be diffrent each time...
another solution?
0

Chief Technology OfficerCommented:
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

Commented:
"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

Chief Technology OfficerCommented:
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

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

Chief Technology OfficerCommented:
Sorry, Brandon.  Didn't see your post, why don't you finish it out -- don't want to take credit for your genius.  :)
0

Commented:
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 Commented:
Thank you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.