question

CThompson72 avatar image
CThompson72 asked

Re-writing a Cursor into a set-based Query

Hello, I'm attempting to re-write a cursor query and I am having some difficulty. My data looks like this: | Column A | Member ID | - - - - - - - - - - - - | NULL | 12345 | | NULL | 12345 | | NULL | 12345 | | NULL | 67890 | | NULL | 67890 | - - - - - - - - - - - - After the query, it needs to look like this: - - - - - - - - - - - - | Column A | Member ID | - - - - - - - - - - - - | 1 | 12345 | | 2 | 12345 | | 3 | 12345 | | 1 | 67890 | | 2 | 67890 | - - - - - - - - - - - - I'm currently accomplishing this via a Cursor. Loop through Member ID, then auto-increment Column A on a per-member basis. Column A must start at 1 and increment by 1, then reset the counter to 1 on the next member. Any help someone can provide in accomplishing this outside of a cursor would be greatly appreciated.
sqlcursorlooploopingcursors
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
You want to look at the ROW_NUMBER function - http://msdn.microsoft.com/en-us/library/ms186734.aspx Something like this: DECLARE @T TABLE ( id1 INT NULL, id2 INT ) INSERT INTO @T ( id2 ) VALUES ( 12345 ), ( 12345 ), ( 12345 ), ( 6789 ), ( 6789 ) SELECT * FROM @t; WITH TempCTE AS ( SELECT id1 , id2 , ROW_NUMBER() OVER ( PARTITION BY id2 ORDER BY id2 ) AS rn FROM @T ) UPDATE TempCTE SET id1 = rn SELECT * FROM @T
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've implemented this into my original query and it now processes over 30 times faster. Thank you ;)
4 Likes 4 ·
be sure to click the tick above so that others know you have got a working answer.
3 Likes 3 ·
You need to include the PARTITION BY clause. See my example above.
1 Like 1 ·
I have tried ROW_NUMBER, it gives me data that looks like this: - - - - - - - - - - - - | Column A | Member ID | - - - - - - - - - - - - | 1 | 12345 | | 2 | 12345 | | 3 | 12345 | | 4 | 67890 | | 5 | 67890 | - - - - - - - - - - - - I haven't been able to get ROW_NUMBER to reset the counter to 1 on a per-member basis.
0 Likes 0 ·
That worked. Thank you very much ;)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.