Add a Unique ID Number To Duplicate Rows SQL

I have the below table called TestTable and need to update the MsgNum column to have a unique number ordered by the date column but grouped by the JobNum.

JobNum | MsgNum | Date 
320    | 0      | 2020-04-10 
320    | 0      | 2020-06-11 
320    | 0      | 2020-06-12 
330    | 0      | 2020-04-10 
330    | 0      | 2020-06-11 
330    | 0      | 2020-06-12

Below is what I need to end up with.

JobNum | MsgNum | Date 
320    | 1      | 2020-04-10 
320    | 2      | 2020-06-11 
320    | 3      | 2020-06-12 
330    | 1      | 2020-04-10 
330    | 2      | 2020-06-11 
330    | 3      | 2020-06-12

I have the below that seems to work to generate the ID number that but suck on how to update the table MsgNum column with the ID number.

SELECT [JobNum], [MsgNum]
    , ROW_NUMBER() OVER (PARTITION BY [JobNum] ORDER BY [Date]) AS id
FROM [dbo].[TestTable]

Answer

You can use row_number() and an updateable common-table-expression:

with cte as (
    select msgNum, row_number() over(partition by jobNum order by Date) rn
    from mytable
)
update cte set msgNum = rn

Advertisement

Answer