Tsql - setting sequential values ​​without a loop / cursor

Question

I need to set a non-unique identifier in the data table. This will be consistent in the group, i.e. for each group, the identifier must begin with 1 and increase the increments of 1 to the last line for this group.

This is illustrated in the table below. The "new identifier" 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 a way to do this without a loop / cursor? If a loop / cursor is the only way, what would be the most efficient code?

thank

Answer 1

ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) UPDATE...FROM subquery 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 , :
• FROM, windows (Row_Index())
• Partition By , reset
• By , NewID

Answer 2

, JOIN, CTE .

;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

-

Answer 3

SELECT 
    UniqueId,
    GroupID,
    ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY UniqueId) AS NewIdx
FROM
    ....

Answer 4

, mssql

--Test table:

DECLARE @t table(Unique_ID int, Group_ID int,   New_ID int)

--Test data:

INSERT @t (unique_id, group_id)
SELECT 1, 1123 UNION ALL SELECT 2, 1123 UNION ALL SELECT 3, 1124 UNION ALL SELECT 4, 1125 UNION ALL SELECT 5, 1125 UNION ALL SELECT 6, 1125 UNION ALL SELECT 7, 1125 

--Syntax:

UPDATE t 
SET new_id = 
    (SELECT count(*) 
    FROM @t 
    WHERE t.unique_id >= unique_id and t.group_id = group_id 
    GROUP BY group_id) 
FROM @t t

--Result:

SELECT * FROM @t 

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

Answer 5

RowNumber(), SS 2000

SELECT  UniqueID,
        GroupID,
        (SELECT COUNT(T2.GroupID)
        FROM    myTable T2
        WHERE   GroupID <= T1.GroupID) AS NewID
FROM    myTable T1