Post reply

compacting a numbered list

  • October 31, 2008 at 12:20 pm

    #195492

    Hey everyone,

    I've got a table with a column of ints and I need to remove any discontinuity between them, ie

    create table #temp (

    num int )

    insert into #temp

    select 1 UNION ALL

    select 2 UNION ALL

    select 4 UNION ALL

    select 10

    and I need to update the table to read 1, 2, 3, 4

    I can do it with a triangular join no problem but I was wondering if there was some trick up your guys' sleeves to do it better.

    ---
    Dlongnecker

  • October 31, 2008 at 12:39 pm

    #892525

    Check BOL on Rank()

    Select Num, Rank() Over( Order By num ) As NumberSequence

    From #temp

    Order By num

  • October 31, 2008 at 12:51 pm

    #892531

    Perfect, thanks!

    ---
    Dlongnecker

  • October 31, 2008 at 7:09 pm

    #892643

    dlongnecker (10/31/2008)

    Hey everyone,

    I've got a table with a column of ints and I need to remove any discontinuity between them...

    Although PW's post is spot on, I suspect there's a bigger picture here... why to you need to do this?

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

  • November 7, 2008 at 6:12 am

    #895467

    I have a table of applications my organization uses, and unlike parents, my organization doesn't love them all the same.

    These applications were ordered by their recovery time but the list wasn't sequential and highly fragmented. (ie, 1, 2, 4).

    Now that they're all ordered it's time to write a SPROC to allow items to be added and re-arranged without disrupting the order.

    ---
    Dlongnecker

  • November 7, 2008 at 6:17 pm

    #895894

    Thanks... and that's a very appropriate use for that code. If it was a huge table with large gaps, I was gonna suggest a different way.

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

  • November 14, 2008 at 6:20 am

    #898518

    Out of curiosity, what would you have suggested?

    ---
    Dlongnecker

  • November 14, 2008 at 6:12 pm

    #898936

    If the only purpose is to find "gaps" in sequential numbers, I've not found anything faster than the following... first, a little bit of test data...

    --===== Create and populate a 1,000,000 row test table.

    -- Column "MyID" has a range of 1 to 1,000,000 unique numbers starting at 82011000000001

    -- Jeff Moden

    SELECT TOP 1000000

    MyID = IDENTITY(BIGINT,82011000000001,1)

    INTO #MyTest

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== A table is not properly formed unless a Primary Key has been assigned

    -- Takes about 3 seconds to execute.

    ALTER TABLE #MyTest

    ADD PRIMARY KEY CLUSTERED (MyID)

    --===== Now, let's add a calculated column to display leading zeros in a "field" of 15 characters

    -- like the original problem.

    ALTER TABLE #MyTest

    ADD DisplayMyID AS RIGHT('000000000000000'+CAST(MyID AS VARCHAR(15)),15)

    --===== Delete some know rows to demo the gap detection code

    -- This deletes 50 rows spaced 2000 apart in the given range

    -- to demo small gaps

    DELETE #MyTest

    WHERE MyID BETWEEN 82011000500001 AND 82011000600000

    AND MyID %2000 = 0

    -- This deletes 100,000 rows in a given range to demo large gaps

    DELETE #MyTest

    WHERE MyID BETWEEN 82011000600001 AND 82011000700000

    ... and then the code to find the gaps...

    --===== Find the "gap ranges"

    -- Finds leading edge of "islands" and then computes the gaps

    -- This assumes that gaps include any whole number greater than 0

    SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1

    FROM #MyTest b

    WHERE b.MyID < a.MyID),

    GapEnd = MyID - 1

    FROM #MyTest a

    WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)

    --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 8 posts - 1 through 7 (of 7 total)

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