Post reply

how to recursively do this?

  • December 6, 2008 at 8:12 pm

    #128334

    I have 1 col in a table, and need to add a second col in the same table, something like following:

    Existing New

    1 1

    1 2

    1 3

    1 4

    2 1

    2 2

    2 3

    2 4

    3 1

    3 2

    3 3

    3 4

    3 5

    how can I write recursive query? Thanks.

  • December 6, 2008 at 10:55 pm

    #908897

    You should be able to use the ranking functions to get what you are looking for. Here is a sample.

    Declare @t Table(i int)

    Insert Into @t

    Select 1

    Union All

    Select 1

    Union All

    Select 1

    Union All

    Select 2

    Union All

    Select 2

    Union All

    Select 3

    Union All

    Select 3

    Union All

    Select 3

    Union All

    Select 3

    Select i ,

    row_number() Over (order by i) + 1 - rank() Over (order by i)

    From @t

  • December 6, 2008 at 11:31 pm

    #908900

    If you throw in the appropriate partition clause, it get's even simpler...

    Declare @t Table(i int)

    Insert Into @t

    Select 1

    Union All

    Select 1

    Union All

    Select 1

    Union All

    Select 2

    Union All

    Select 2

    Union All

    Select 3

    Union All

    Select 3

    Union All

    Select 3

    Union All

    Select 3

    SELECT i, ROW_NUMBER() OVER (PARTITION BY i ORDER BY i) AS New

    FROM @t

    Now, what made you think it had to be done recursively? 😛

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

  • December 7, 2008 at 6:51 am

    #908920

    Thanks Jeff.

    I knew there was a way to do it without the math, I just cound't think of it last night.

  • December 7, 2008 at 9:26 am

    #908932

    Thank you guys, it worked beautifully in my code.

  • December 7, 2008 at 9:49 am

    #908933

    Ken Simmons (12/7/2008)

    Thanks Jeff.

    I knew there was a way to do it without the math, I just cound't think of it last night.

    S'ok... Heh... not much coffee at night. 😛

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

  • December 7, 2008 at 9:50 am

    #908934

    Yihong (12/7/2008)

    Thank you guys, it worked beautifully in my code.

    You bet... thanks for the feedback.

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

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