Post reply

Avoiding cursors

  • April 9, 2009 at 7:25 am

    #130550

    I have a two column table:

    TRX_CODE TRXID

    AAA1 19212312

    AAA1 19212313

    AAA1 19212314

    AAA2 19212315

    AAA2 19212315

    etc (5,000,000 records, TRXID is a unique value)

    What I want to end up with is

    TRX_CODE TRXID

    AAA1 1

    AAA1 2

    AAA1 3

    AAA2 1

    AAA2 2

    and it's important I preserve the order of TRXID

    I'm currently doing this by

    DECLARE @Foo varchar(5)

    DECLARE foo CURSOR FOR SELECT DISTINCT TRX_CODE FROM table

    FETCH NEXT FROM foo INTO @Foo

    then for each TRX_ID

    DECLARE foo2 CURSOR FOR SELECT TRXID FROM table WHERE TRX_CODE=@foo ORDER BY TRXID

    DECLARE @bar int

    DECLARE @ID int

    OPEN foo2

    FETCH NEXT FROM foo2 INTO @ID

    SET @bar=1

    UPDATE table SET TRXID=@bar WHERE TRXID=@ID

    SET @bar=@bar+1

    and then looping round to the next TRXID

    Now this works (eventually), but I don't like the cursor-within-a-cursor approach. Can anyone suggest a more set-based approach?

    Thanks

    --
    Scott

  • April 9, 2009 at 7:38 am

    #975032

    Scott (4/9/2009)

    I have a two column table:

    TRX_CODE TRXID

    AAA1 19212312

    AAA1 19212313

    AAA1 19212314

    AAA2 19212315

    AAA2 19212315

    etc (5,000,000 records, TRXID is a unique value)

    What I want to end up with is

    TRX_CODE TRXID

    AAA1 1

    AAA1 2

    AAA1 3

    AAA2 1

    AAA2 2

    and it's important I preserve the order of TRXID

    TRXID certainly isn't a unique value in the dataset you want to end up with:ermm:

    Looks like you want to create row numbers on TRXID, partitioned on TRX_CODE?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • April 9, 2009 at 7:43 am

    #975040

    That's correct. TRXID is a unique value in my source data, but not in my desired output. I should also add that in the source I can't guarantee that the TRXID values for any TRX_CODE are consecutive.

    --
    Scott

  • April 9, 2009 at 7:45 am

    #975043

    There is no need for cursor to do this, use Row_Number()

    SELECT TRX_CODE,

    ROW_NUMBER() OVER (PARTITION BY TRX_CODE ORDER BY TRXID) AS TRXID FROM YourTable

  • April 9, 2009 at 7:55 am

    #975055

    Ooh - that's much better. I knew I was looking at this the wrong way. Thank you.

    --
    Scott

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

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