Update column with the max of a group-CodePudding

In SQL Server, let's consider this table:

Id	PartNumber	Timestamp	Reintegration
1	1111	2020-01-01	NULL
2	1111	2020-01-02	NULL
3	2222	2021-02-01	NULL
4	2222	2021-02-02	NULL

I want to update the Reintegration column with the MAX value of a Group By PartNumber.

Here is the expected result :

Id	PartNumber	Timestamp	Reintegration
1	1111	2020-01-01	NULL
2	1111	2020-01-02	2020-01-02
3	2222	2021-02-01	NULL
4	2222	2021-02-02	2021-02-02

I have tried a lot of thing with GROUP BY, MAX, INNER JOIN, without success.

Thanks for any help.

CodePudding user response：

The window function max() over() seems like a nice fit here.

Example or dbFiddle

with cte as (
Select *
      ,MaxDate = max([TimeStamp]) over (partition by PartNumber) 
 from YourTable
)
Update cte set [Reintegration] = MaxDate
 Where [TimeStamp] = MaxDate

The Updated Table

Id  PartNumber  Timestamp     Reintegration
1   1111        2020-01-01    NULL
2   1111        2020-01-02    2020-01-02
3   2222        2021-02-01    NULL
4   2222        2021-02-02    2021-02-02