1

When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove?

DELETE FROM dbo.ATTENDANCE 
    WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE 
    GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

And this works great. The reason why I am using this one is because the only unique id available is one in the IDENTITY column. And to determine if there are duplicate rows I have to look at a combination of multiple columns.

But if I have a set of duplicate rows how do I / how does SQL Server decide which to remove? And how would I force it to remove all but the one with the highest IDENTITY value?

EMPLOYEE_ID     ATTENDANCE_DATE     AUTOID
A001            2011-01-01          1
A001            2011-01-01          2

If I would run the query now it happens to remove the second one, with AUTOID 2. But I am trying to remove all but this one (because this is the one latest added).

6

You could implement a query using row_number() to delete everything but the most recent row. This partitions the data by the employee_id and orders it by the autoId column, then you delete everything that is greater than the first row number:

;with cte as
(
  select [EMPLOYEE_ID], [ATTENDANCE_DATE], [AUTOID],
    row_number() over(partition by [EMPLOYEE_ID], [ATTENDANCE_DATE] 
                      order by  [AUTOID] desc) rn
  from dbo.ATTENDANCE
)
delete 
from cte 
where rn > 1;

See SQL Fiddle with Demo

  • 1
    Thank you very much! So in this query if I want to find a duplicate row by looking at a combination of multiple columns, I just add them to partition by? eg. partition by [EMPLOYEE_ID], [ATTENDANCE_DATE] – Ben Z. May 24 '13 at 16:10
  • I actually just tried it with the SQL Fiddle. This works great. Thanks! – Ben Z. May 24 '13 at 16:11
  • @BenZ. Yes that is correct, if you have multiple dates for each employee then you can change the partition to the employee_id and attendance_date. I updated my answer to include a partition by both date and employee. – Taryn May 24 '13 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.