SQl Server Merge Adjacent duplicate rows-CodePudding

Table Data is as follows, I would like to select minimum of the updated date with Name. Need to merge name if adjacent names are same. and Select the records in Ascending order of date updated

Name UpdatedDate
L     05-May-2022
S     09-May-2022
S     09-May-2022
A     10-May-2022
A     15-May-2022
L     16-May-2022
S     17-May-2022
S     19-May-2022
K     01-May-2022

Expected Output

Name UpdatedDate
K     01-May-2022 
L     05-May-2022
S     09-May-2022 
A     10-May-2022 
L     16-May-2022
S     17-May-2022

CodePudding user response：

Assuming that:

Your dates are always in ascending order AND
Your version of SQL Server supports LEAD and Lag functions

You can try this:

SELECT NAME, UPDATEDDATE
FROM
(SELECT NAME, UPDATEDDATE, LAG(NAME, 1, 'DEFAULTVALUE') OVER (ORDER BY UPDATEDDATE) AS LAGNAME) T
WHERE T.NAME <> T.LAGNAME

This uses lag to find which rows belong to the same "group" based on the Name column, and then takes the first row, which should also have the earliest date going by assumption 1.

CodePudding user response：

Using Group By. Select Name, UpdatedDate from YourTable Group By Name, UpdatedDate