missing rows 2

[mercwrought]

Hi all, got another question. I have a table that has a row idnumber that can not be null and contains an int between 1 and 500000 and I need to find the missing numbers. Like I have row 1 and 2 but there is no row 3. I had an idea but then I do not believe it is on the right track.

 select * from redpass r1 left join redpass r2 on r1.idnumber = r2.idnumber + 1  where r2.idnumber is NULL Order by r1.idnumber

any ideas?

 

[checkai]

can you do this within a stored procedure?

 

[checkai]

what about something like this?

create table #Numbers(
	ID int)

declare @i as integer;
set @i = 500;

while @i <= 500
begin
  insert into #Numbers
  Select @i

  Set @i = @i + 1
End

select 	x.ID 
from 	#Numbers x
	left join YourTable i on x.ID = i.ID
Where	i.ID is null

 

[r937]

you were so close!!

select r1.idnumber + 1   as missing
  from redpass r1 
left outer
  join redpass r2 
    on r1.idnumber + 1 
     = r2.idnumber 
 where r2.idnumber is null 
order 
    by r1.idnumber + 1

the above query finds first missing number in each gap

i.e. if the gap is more than one number, 2nd and subsequent numbers in the gap will not be found -- if you need these, then a LEFT OUTER from an integers table is required

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[mercwrought]

I was so close I bet if I did not go to that staff meeting and had a few more minutes before I gave up and posted it I would have got it. Its ok, I like both answers I will have to go with the table because I’m filling in the empty spaces with accounts from a subsystem. Thx for all of your answers.