Find Duplicates in SQL Server Table

Question

Somehow there are duplicate entries in my table. Data structure is set-up like this:

RecordID, UserID, clockin, clockout

So if one user has 2 entries with the same clockin/clockout time then it is a duplicate. A duplicate would look like this. The entry should only exist once, and the clockin & clock out time are exactly the same.

RecordID  UserID  clockin              clockout
1         16      2015-01-12 07:15:23  2015-01-12 08:55:15
2         16      2015-01-12 07:15:23  2015-01-12 08:55:15
3         44      2014-12-18 08:02:36  2014-12-18 04:02:36
4         44      2014-12-18 08:02:36  2014-12-18 04:02:36

Answer 1

With row_number window function:

with cte as(select *, row_number() over(partition by UserID, clockin, clockout 
                                        order by RecordID ) as rn from TableName)
delete from cte where rn > 1

Answer 2

First, you need to find the duplicated records and delete them, leaving only one record for each combination of UserId, clockin, clockout values.

DELETE 
FROM MyTable
WHERE RecordId IN(
    SELECT t1.RecordId
    FROM MyTable t1
    INNER JOIN MyTable t2 ON(t1.UserId = t2.UserId 
                         AND t1.clockin = t2.clockin 
                         AND t1.clockout = t2.clockout)
    WHERE t1.RecordId < t2.RecordId
)

Then you should set up a unique constraint or unique index on the combination of those 3 columns, so that Sql Server will prevent you from entering duplicated data.