How to delete duplicate rows in sql server

Uday Yadav

7y917

How to delete duplicate rows in sql server.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vivek Kumar7y ago
Accepted answer
You can refer below link for better understanding.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/
Priyanka Singh7y ago
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)

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) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1
Vivek Kumar7y ago
Yes, you can use without row_number also. Please have a look to below link.

https://stackoverflow.com/questions/37560593/delete-duplicate-records-without-using-row-number-function
Uday Yadav7y ago
How can i use without row_number
Vivek Kumar7y ago
Hi,
Have a look to below links for your reference.

https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server