In this blog, I would like to share a simple way to delete the duplicate record from a table, using ROW_NUMBER ()

Step 1

Table structure
  1. CREATE TABLE #Employee (
  3. EmpID INT IDENTITY(1001, 1)
  5. , EmpName VARCHAR(200)
  7. , EmpSalary INT
  9. )

Step 2

Query for inserting record into an Employee table
  1. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
  2. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
  3. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
  4. INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)
  5. INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)
  6. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
  7. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
  8. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
  9. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
  10. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
  11. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
  12. INSERT# Employee(EmpName, EmpSalary) Values('Venkata T', 50000)

Step 3

#Employee table contains duplicated records given below.

SELECT * FROM #Employee

Step 4
Delete the duplicate record, using ROW_NUMBER ()
  1. SELECT EmpID, EmpName, EmpSalary, ROW_NUMBER() OVER(PARTITION BY EmpName, EmpSalary ORDER BY EmpID) AS RowNumber INTO# EmployeeFinal
  2. FROM# Employee
  3. DELETE
  4. FROM# Employee
  5. WHERE EmpID IN(SELECT EmpID FROM# EmployeeFinal WHERE RowNumber > 1)

Step 5

After the deletion of the duplicate rows from the #Employee table, it displays, as shown below.