How do I delete duplicate rows in SQL Server using the OVER clause?

Question

Here are the columns in my table:

Id
EmployeeId
IncidentRecordedById
DateOfIncident
Comments
TypeId
Description
IsAttenIncident

I would like to delete duplicate rows where EmployeeId, DateOfIncident, TypeId and Description are the same - just to clarify - I do want to keep one of them. I think I should be using the OVER clause with PARTITION, but I am not sure.

Thanks

Answer 1

If you want to keep one row of the duplicate-groups you can use ROW_NUMBER. In this example i keep the row with the lowest Id:

WITH CTE AS 
(
    SELECT rn = ROW_NUMBER() 
                OVER( 
                  PARTITION BY employeeid, dateofincident, typeid, description 
                  ORDER BY Id ASC), * 
    FROM dbo.TableName
) 
DELETE FROM cte 
WHERE  rn > 1 

Answer 2

use this query without using CTE....

delete a from (select id,name,place, ROW_NUMBER() over (partition by id,name,place order by id) row_Count from dup_table) a where a.row_Count >1

Answer 3

You can use the following query. This has an assumption that you want to keep the latest row and delete the other duplicates.

DELETE [YourTable]
FROM [YourTable]  
LEFT OUTER JOIN (  
   SELECT MAX(ID) as RowId 
   FROM [YourTable]   
   GROUP BY EmployeeId, DateOfIncident, TypeId, Description  
) as KeepRows ON  
   [YourTable].ID = KeepRows.RowId  
WHERE  
   KeepRows.RowId IS NULL