Guys, I think I have a rather simple problem, but I could not get it solved. Anyone out there to help me out. OK, here is the problem. Lets say I have a temp table #temp that look like the on below: COLUMN_1 SEQUENCE_ID-------- -----------A NULLA NULLB NULLB NULLB NULLC NULLC NULLC NULLC NULLWhat I want is the #temp table to look like this: COLUMN_1 SEQUENCE_ID-------- -----------A 1A 2B 1 <- The sequence_id needs to be resetB 2 to 1 when the row value in COLUMN_1B 3 changesC 1C 2C 3C 4
Seing from the above, the sequencing for SEQUENCE_ID is by COLUMN_1 aggregation. The real catch is I do not want to use while loops or cursors, I want to update using single line update like the one below: [I got this code off the SQL Team website] [http://www.sqlteam.com/item.asp?ItemID=765] [Creating a Sequential Record Number field ] [graz on 9/25/2000 in UPDATE ] ------------------------------------------------------------ declare @intCounter int set @intCounter = 0 update Yaks SET @intCounter = YakSequenceNumber = @intCounter + 1------------------------------------------------------------However the code above only sequences in the following manner: COLUMN_1 SEQUENCE_ID-------- -----------A 1A 2B 3 B 4B 5C 6C 7C 8C 9
So I need to to tweak the statement to number the sequence by aggregation. I think I've done it some time back, but I could not recall it. So help would be really appreciated. Thanks in advance and regard Xega Whenever I start to think, my brain freezes therefore I think I'm cool...
Aggregated Row sequence numbering
Posted - 2002-08-22 : 20:24:07
