SQL Query 1

[fiber0pti]

I am trying to create a query where SQL selects a break point in a row of numbers. I have a cloumn containing a number, lets say 1-100. They all exsist except 50. How would I get SQL to tell me that 50 is not in use?

 

[clapag22]

create proc find_gaps @max_num int as

set nocounton

declare @counter int

select @counter = 1

create table #missing_nums (missing_num int)

while @counter <= @max_num
begin

if not exists (select * from mytable where myfield = @counter)

begin

insert into #missing_nums values (@counter)

end

select @counter = @counter + 1

end

select * from #missing_nums

GO

 

[bperry]

I like clap's approach.

If you need a solution in a single SQL statement, you may like to try this. It ALMOST works... it will report single gaps beautifully. If you have a bigger gap, say 50 and 51, then this statement will report that as only a single gap. If that works for you, then great. If not, perhaps another forum reader can improve this for us.

select (g1.pkId + 1) as 'Gap!'
from gaps g1
Left JOIN gaps g2
on g1.Pkid + 1 = g2.PkId
where g2.PkId IS NULL and
g1.PkId <> (Select MAX(pkid) from gaps)
order by g1.PkId

bperry

 

[fiber0pti]

bperry, I think your solution might work better. I'm not an expert at SQL. I tried this:
select (OrderID + 1) as 'Gap!'
from orders g1
Left JOIN orders g2
on OrderID + 1 = OrderID
where OrderID IS NULL and
OrderID <> (Select MAX(OrderID) from Orders)
order by OrderID


and I got a bunch of errors. I'm not sure what g1.pkId and g2.PkId. I have one table and one column I'm pulling data from. Could you elaborate on this?
Thanks!

 

[bperry]

Yuo have to change the table name and column name.
Everywhere I have gap, change to Orders.
Everywhere I have PkId, change to OrderId.
This is because on yuor original post, you did not mention your names, so I had to invent my own.

Perhaps like this, if I did not make any errors:
--------------------
select (g1.OrderId + 1) as 'Gap!'
from Orders g1
Left JOIN Orders g2
on g1.OrderId + 1 = g2.OrderId
where g2.OrderId IS NULL and
g1.OrderId <> (Select MAX(OrderId) from Orders)
order by g1.OrderId
---------------------

bperry