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





2 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's actually a cool and useful piece of info. I am glad that you simply shared this useful info with us. Please keep us up to date like this. Thanks for sharing, You can also check out this http://www.sqiar.com

    ReplyDelete