How to remove 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 remove duplicate lines where EmployeeId, DateOfIncident, TypeId and Description same - just to clarify - I want to save one of them. I think I should use the OVER clause with PARTITION , but I'm not sure.

thanks

Answer 1

If you want to keep one row of duplicate groups, you can use ROW_NUMBER . In this example, I hold the line with the smallest 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 ....

remove a from (select id, name, place, ROW_NUMBER () over (section by identifier, 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 assumes that you want to keep the last line and remove 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