Avoiding cursors

• Scott-144766

  SSCarpal Tunnel

  Points: 4333

  More actions

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• Scott-144766

  SSCarpal Tunnel

  Points: 4333

  More actions

  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

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  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

• Scott-144766

  SSCarpal Tunnel

  Points: 4333

  More actions

  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