tsql - Setting sequential values without looping/cursoring

Question

I need to set a non-unique identifier in a data table. This would be sequential within a group ie. for each group, the ID should start at 1 and rise in incremements of 1 until the last row for that group.

This is illustrated by the table below. "New ID" is the column I need to populate.

Unique ID  Group ID  New ID
---------  --------  ------
1          1123      1
2          1123      2
3          1124      1
4          1125      1
5          1125      2
6          1125      3
7          1125      4

Is there any way of doing this without looping/cursoring? If looping/cursoring is the only way, what would the most efficient code be?

Thanks

Answer 1

One method is to use ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) in an UPDATE...FROM statement with a subquery in the FROM clause.

update MyTable set NewID = B.NewID
from
MyTable as A
inner join (select UniqueID, ROW_NUMBER() over (partition by GroupID order by UniqueID) as NewID from MyTable) as B on B.UniqueID = A.UniqueID

• MSDN has a good sample to get you started:
• You need to utilize a subquery in the FROM clause in order to utilize a windows function (Row_Index())
• Partition By tells the server when to reset the row numbers
• Order By tells the server which way to order the group's NewID's

Answer 2

I agree with Damien's point in the comments but you don't need a JOIN you can just update the CTE directly.

;WITH cte AS
(
SELECT [New ID],
       ROW_NUMBER() OVER (PARTITION BY [Group ID] ORDER BY [Unique ID]) AS _NewID
FROM @T       
)
UPDATE cte
SET [New ID] = _NewID

Online Demo