Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find all not existsing rows in a range?

Posted on 2008-10-14
11
Medium Priority
?
272 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 61

Accepted Solution

by:
Kevin Cross earned 1600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

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

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 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1600 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 400 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 61

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

579 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