Find the first available No.

Somebody messed up with the design of one of my tables. The table has values like 1,2,, 3,8,99. I have to locate the first blank in there and insert value.

I know there is simple way to do that but just can't recall it. How do I do that?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Your question is not clear. Are you trying to find a gap in sequential numbers or a missing value in a string?

If it's a gap in sequential numbers

declare @t table (i int)
insert into @t (i) select 1 union all select 2 union all select 4 union all select 8

declare @i int
select @i = 0

select top 1 t1.i +1
from @t t1
left join @t t2
on t1.i + 1 = t2.i
where t2.i is null
and t1.i < (select max(i) from @t)
order by 1
If it is a gap in sequential numbers and you can update them it's even more simple

declare @i int
select @i = 0 -- or any other seed-1
update @t SET @i = i = @i + 1
select * from @t

If it's a missing value in a string, check this
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1057

Bambola.

If we are to achieve results never before accomplished, we must expect to employ methods never before attempted. - Francis Bacon

Thanks Bambola. My Query was for gap between sequenced numbers. I'll try your solution and will post if I have any problems.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

You may also use exists (or rather not exists) instead of join like this:

select top 1 i + 1
from @t t1
where not exists (select i from @t t2 where t2.i = t1.i + 1)
and t1.i < (select max(i) from @t)
order by 1

Hans

..And to find all missing numbers in the sequence (Just in case):

declare @t table(i int)
declare @count int
SET @count=0
WHILE @count<(select max(SequencedField) from YourTable)
BEGIN
SET @count=@count+1
Insert @t(i) VALUES (@count)
END
Select i from @t where i NOT IN(select SequencedField from YourTable)




Nathan H.O.
Moderator
SQL-Server-Performance.com