Post reply

find a missing no

  • August 17, 2006 at 6:13 am

    #173899

    i have a table name bill.having fields billid ,bill no and so on..bill id is autoincreamented...and billno is stored like 1,2,3,6,7,8..wat i have to do i want a billno tht is missing..eg i have billno 1,2,3,4,6,...5 is not in the table...i want to write a query tht get the missing no...i'e 5...

    can any1 help me

  • August 17, 2006 at 6:35 am

    #655426

    -- prepare test data

    declare @bill table (billno int)

    insert @bill

    select 1 union all

    select 2 union all

    select 3 union all

    select 4 union all

    select 6

    -- do the work

    SELECT     b.billno - 1 MissingNo

    FROM       @bill b

    LEFT JOIN  @bill w ON w.billno = b.billno - 1

    WHERE      b.billno > (SELECT MIN(billno) FROM @bill)

               AND w.billno IS NULL

    N 56°04'39.16"
    E 12°55'05.25"

  • August 17, 2006 at 7:38 am

    #655447

    -- Give this a try as well

     

    DECLARE @max_id int

    DECLARE @i int

    DECLARE @testTable TABLE (id INT)

    SET @max_id = (SELECT MAX(ID) FROM Bill)

    SET @i = 0

    WHILE @i < @max_id

    BEGIN

       INSERT INTO @testTable (id) VALUES(@i)

       SET @i = @i + 1

    END

    SELECT *  FROM @testTable tt WHERE tt.id NOT IN (SELECT ID FROM BILL)

  • August 17, 2006 at 7:40 am

    #655449

    What if you have some million rows? Then that will take some time to complete...

    N 56°04'39.16"
    E 12°55'05.25"

  • August 17, 2006 at 10:02 am

    #655489

    Thank you all for your kind help, with your guidence i have resolved my issue

  • August 21, 2006 at 5:40 pm

    #656027

    Peter,

    Great idea - I did notice that it doesn't catch those instances where there is more than 1 consecutive number misssing in the sequence.

    -- prepare test data

    declare @bill table (billno int)

    insert @bill

    select 1 union all

    select 2 union all

    select 3 union all

    select 4 union all

    select 6 union all

    select 7 union all

    select 9 union all

    select 10 union all

    select 11 union all

    select 12 union all

    select 15 union all

    select 16

    -- do the work

    SELECT     b.billno - 1 MissingNo

    FROM       @bill b

    LEFT JOIN  @bill w ON w.billno = b.billno - 1

    WHERE      b.billno > (SELECT MIN(billno) FROM @bill)

               AND w.billno IS NULL

    (12 row(s) affected)

    MissingNo  

    -----------

    5

    8

    14

    (3 row(s) affected)

     

  • August 21, 2006 at 8:39 pm

    #656041

    I can't take the credit for this one but can't remember who first did it on this forum... it's the fastest "Missing ID" finder I've ever seen... all I did was write the test for it... the last tiny little select finds the missing ID's... and it takes just seconds on huge, properly indexed tables... AND IT FINDS RANGES OF MISSING IDS!!!!

    AND, LOOK MA!!! NO LOOPS!!!

    Here's everything I have on it including the test setup....

    --===== Setup for speed and to prevent blocking

        SET NOCOUNT ON

        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    --=============================================================================

    --      Create an experimental table to simulate the table being examined

    --=============================================================================

    --===== If the experimental temp table exists, drop it so we can do repeat runs

         -- if we want to "play".

         IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

            DROP TABLE #yourtable

    --===== Create the experimental temp table and populate with IDs on the fly

         -- This always works because SYSCOLUMNS always has at least 256 entries

         -- even in a new database and 256*256*256 > 1,000,000

     SELECT TOP 1000000 ID = IDENTITY(INT, 1, 1)

       INTO #yourtable

       FROM dbo.SYSCOLUMNS sc1,

            dbo.SYSCOLUMNS sc2,

            dbo.SYSCOLUMNS sc3

    --===== Like any good table, our experimental table needs a Primary Key

      ALTER TABLE #yourtable

            ADD PRIMARY KEY CLUSTERED (ID)

    --===== Ok, we have an experimental table with a million IDs from 1 to 1,000,000.

         -- Let's remove some of the rows and see if we can find if they're missing

         -- This deletes a handful of individual rows and 3 very small ranges

     DELETE #yourtable

      WHERE ID IN (2,3,250,251,2000,4000,4002,4004,900001,900002,900003,999999)

         -- This deletes a "monster" range just to see how it's handled.

     DELETE #yourtable

      WHERE ID BETWEEN 500000 AND 700000

    or (ID BETWEEN 700000 AND 800000

       AND ID%2 = 0)

    --===== This short little ditty is what does the actual work

     SELECT MinRange = (SELECT ISNULL(MAX(suba.ID),0)+1

                          FROM #yourtable suba

                         WHERE suba.ID < a.ID),

           MaxRange = ID - 1 

      FROM #yourtable a

     WHERE a.ID - 1 NOT IN (SELECT ID FROM #yourtable)

       AND a.ID - 1 > 0

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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