How to Delete Duplicate records or rows from a Table in SQL Server

SQL Server ROW_NUMBER() Function with Common Table Expressions (CTEs) to Delete the Duplicate records or rows from a Table

WITH MyCTE AS

       (

         Select* From 

            ( SELECT *, ROW_NUMBER()OVER(Partition by EMP_ID 
                 Order by Dept_Name) Rankx FROM Tbl_DupData 
              ) S1

         WHERE Rankx >1 
        )

Delete FROM MyCTE

SELECT *FROM Tbl_DupData

     ;

GO

Example:

Let us Suppose we have a Table with Duplicate records as follows:
Select*from Tbl_DupData

Now we have to identify the duplicate records using the ROW_NUMBER() function on above table as follows :

SELECT *, ROW_NUMBER() OVER(Partition By EMP_ID Order By Dept_Name) Rankx FROM Tbl_DupData

If you want to Select only the Duplicate records from above table, we can do as follows:
Select * From 

    (Select *, ROW_NUMBER() OVER(Partition By EMP_ID Order By Dept_Name) Rankx                 FROM Tbl_DupData 

    ) S1 WHERE S1.Rankx >1

To delete these Duplicate records from the Table, we have to use the Common Table Expression method as follows :

WITH MyCTE AS

       (

         Select* From ( SELECT *, ROW_NUMBER() OVER(Partition By EMP_ID 
         Order By Dept_Name) Rankx FROM Tbl_DupData 
                              ) S1

         WHERE Rankx >1 
        )

Delete FROM MyCTE

SELECT *FROM Tbl_DupData

     ;

GO

Final Output without duplicates :

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------