Delete duplicate rows in sql

we will discuss deleting all duplicate rows except one from a sql server table. 

Let me explain what we want to achieve. We will be using Employees table for this demo.


SQL Script to create Employees table

Create table Employees

(

     ID int,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

GO

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

The delete query should delete all duplicate rows except one. The output should be as shown below, after the delete query is executed.


Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions.

WITH EmployeesCTE AS

(

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

   FROM Employees

)

DELETE FROM EmployeesCTE WHERE RowNumber > 1