Delete all duplicate rows except one from a SQL Server table

Delete all duplicate rows except one from a SQL Server table

All the duplicate rows from below table should be deleted except one.

After delete operation table should look like this:

First we need to figure out the duplicate rows  and to get it done we will add one extra column whose value will be generated using ROW_NUMBER ( ) inbuilt function with Partition By , so that row number will be generated for each partition, Basically it should look like as shown below.

After generating the row_number for each groups, we will create a CTE and Delete all the rows having row number >1 (duplicate rows) .

Final Query:

WITH EmployeeCTE AS

(

SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) 

AS RowNumber

from Employee

)

DELETE FROM EmployeeCTE WHERE RowNumber >1