sql query to delete duplicate based on employee name

 -- Assume we have a table called Employees with columns Id, Name, and Salary

-- Assume we want to keep the record with the highest salary for each employee name

-- Use a common table expression (CTE) to rank the records by salary for each employee name

WITH CTE AS

(

    SELECT Id, Name, Salary,

    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Salary DESC) AS RowNum

    FROM Employees

)

-- Delete the records from the CTE where the row number is greater than 1

DELETE FROM CTE

WHERE RowNum > 1