Find first missing number

  • I have a table with a column which is not a primary key but which I increment by 1 each time a new record is added.

    So, the rows will end up with 1 in the first row, 2 in the second etc.

    The thing is any row might subsequently be deleted. So I could end up with say 5 rows and then one of them gets deleted. So I might end up with 1,2,4,5 as the values in that column.

    If that happens, next time I add a new record, I want to put the missing one back - in this case the '3'.

    How can I find the first row whose value in that column is not 1 more than the row before? And grab the value of the column in the row before so I can add 1 to it when I insert the new row.

    I know I can do this with a cursor - is there any way of doing it without a cursor?

    Thanks for any help.

  • Here's one way:

    select min(YourColumn)

    from dbo.YourTable as T1

    where not exists

    (select 1

    from dbo.YourTable as T2

    where T2.ID = T1.ID + 1);

    You'll have to plug in your actual table and column names, of course. Does that do what you need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Here's another way (note that it uses a virtual tally table - if you already have one, jump to the final select statement). If there are no gaps, this also gets the next number. You need to ensure that the tally table has enough rows being returned to cover all the rows in your table.

    ;WITH

    Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

    Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

    Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

    Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

    SELECT TOP (1) N

    FROM Tally

    LEFT JOIN <YourTable> yt

    ON yt.<YourColumn> = Tally.N

    WHERE yt.YourColumn IS NULL

    ORDER BY N

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • GSquared - thanks very much for that - it does exactly what I need.

    Wayne, thanks for your reply. I keep reading about the benefits of a tally table but have never needed one before. So I've created one now and can (finally) see what they're for.

    Thanks again.

  • Can you mark posts as resolved on this forum?

  • sku370870 (3/23/2010)


    Can you mark posts as resolved on this forum?

    No, because you never know when someone else might find the question or discussion interesting and make another contribution.

    Hey Wayne, nice in-line tally table 😀 😉

  • Paul White NZ (3/24/2010)


    sku370870 (3/23/2010)


    Can you mark posts as resolved on this forum?

    No, because you never know when someone else might find the question or discussion interesting and make another contribution.

    Hey Wayne, nice in-line tally table 😀 😉

    Thanks... just goes to show that I can still learn better ways. Thanks for pointing out the UNION ALL;-)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply