Solved

Find all not existsing rows in a range?

Posted on 2008-10-14
11
265 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 59

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

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

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 59

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 59

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

822 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