How to delete duplicate rows in sql server

Question

How to delete duplicate rows in sql server.

Vivek Kumar · 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 Singh · Answer

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 Kumar · Answer

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 Yadav · Answer

How can i use without row_number

Vivek Kumar · Answer

Hi, Have a look to below links for your reference. https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server

How to delete duplicate rows in sql server

Answers (5)