Saturday 5 April 2014

Delete duplicate rows using Row_number and CTE in SQL

                   We can use Row_Number to delete duplicate rows from a table, as we know Row_Number function allocates serial number based on a key column.

Consider a table named 'EmpDetails' which has data as below:



As seen in the above screen shot there are duplicate records for EmpId 1,2 and 3

By using CTE and Row_number function we can remove the duplicate records from the above table. The query to remove duplicate values is as follow:

Query:

With CTE as
(select *,ROW_NUMBER() over(partition by empid order by empid) as Row_num from dbo.EmpDetails)

delete from CTE where Row_num > 1

This query will delete the duplicate rows from the EmpDetails table

Now, if we see the data in the EmpDetails Table it will contain only unique values in EmpID column.

Select * from dbo.EmpDetails





15 comments:

  1. You have a data corruption issue in the case you outlined above, not a duplicate row.
    if 1, A appeared 2x, that would be duplicate.

    ReplyDelete
  2. It is informative post. Thanks for sharing delete duplicate row. I read your post and my problem is solve. I shared this post Car towing service. Keep posting.

    ReplyDelete