SQL Server Performance

Find the first available No.

Discussion in 'General Developer Questions' started by gaurav_bindlish, Aug 22, 2003.

  1. gaurav_bindlish New Member

    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
  2. bambola New Member

    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
  3. gaurav_bindlish New Member

    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
  4. Hans New Member

    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
  5. vbkenya New Member

    ..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

Share This Page