4

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

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Bill Greer
  • 2,886
  • 8
  • 45
  • 71

3 Answers3

18

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
Tim Schmelter
  • 429,027
  • 67
  • 649
  • 891
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

Ashok D
  • 31
  • 1
0

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
Rahul Kargwal
  • 1