Post reply

how find the missing value from a range of value

  • November 11, 2008 at 9:20 pm

    #70944

    hi all,

    i have a column which is primary key column, i have given two options to user 1. autocomplete 2.manual. It means user can give manual code or I will generate if user is not giving manual code, but I have a limitation till 99999. I mean the column contains a maximum value of 99999. Assume that user has given 99999 as manual code and a record is inserted with that code. So, next time when user has not given any code then i need to find out all the missing values of that column.

    So that i can generate a code with minimum value and insert the record.

  • November 11, 2008 at 9:47 pm

    #897322

    Sounds like a good use of a Tally table. Read the following article, it should help you out. If not, let us know.

    http://qa.sqlservercentral.com/articles/TSQL/62867/

  • November 11, 2008 at 10:02 pm

    #897327

    Actually, there's a faster method than the Tally table for this...

    SELECT TOP 1 (SELECT ISNULL(MAX(b.SerialNumber),0)+1

    FROM #JbmTest b

    WHERE b.SerialNumber < a.SerialNumber) AS AvailableNumber

    FROM #JbmTest a

    WHERE a.SerialNumber - 1 NOT IN (SELECT SerialNumber FROM #JbmTest)

    AND a.SerialNumber - 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)
    Intro to Tally Tables and Functions

  • November 11, 2008 at 10:09 pm

    #897333

    Veterans Day, my brain is taking a slight break.

  • November 12, 2008 at 6:19 am

    #897457

    Heh... mine too... I copied it from a gap detecter that I had in my library. It's some really ugly code with the correlated sub-query and all, but it's amazingly fast. Some guy posted it on another forum about 5 years ago and I didn't comment his name into the code. I don't even remember which forum it was. I tweaked it a bit, but the original idea is his.

    --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)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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