Q:

SQL Server Duplicate Payroll Records

I have been trying to find a way to select/delete the duplicated payroll records from my dataset below.
   ID    HDate      SiteID  RecordID    EmployeeID  TimeWorked
8066839  24/01/2015  164         840          28930       240
7982224  08/11/2014  164         840          28812       390
8066838  23/01/2015  164         839          28930       360
7982223  07/11/2014  164         839          28812       240
8066837  22/01/2015  164         838          28930       330
7982222  06/11/2014  164         838          28812         0

Duplicate is the RecordID and needs to be unique for each SiteID.
I have not been able to find a way of retrieving the lowest 'ID' for each 'RecordID'. I use the below to find my duplicates when required against each site. 
    SELECT     *
FROM         StaffHours
WHERE     (SiteID = '164') AND (RecordID IN
                          (SELECT     RecordID
                            FROM          StaffHours
                            WHERE      (SiteID = '164')
                            GROUP BY RecordID
                            HAVING      (COUNT(RecordID) > 1)))
ORDER BY RecordID DESC

I was attempting something like this, but couldnt get it to work.
    declare dup_id cursor for
SELECT    * FROM         StaffHours
WHERE     (ID IN
                          (SELECT     MIN(ID) AS Expr1
                            FROM          StaffHours
                            WHERE      (SiteID = '25') AND (RecordID IN
                                                       (SELECT     RecordID
                                                         FROM          StaffHours
                                                         WHERE      (SiteID = '25')
                                                         GROUP BY RecordID
                                                         HAVING      (COUNT(RecordID) > 1)))));
                open dup_id;
fetch next from dup_id;
while @@fetch_status = 0
                begin
                                fetch next from dup_id;
                end;
                close dup_id;
                deallocate dup_id
                go

Any help would be greatly appreciated.
Thanks,
Adam

A:

I think you were very close to solving this yourself:
SELECT     *
FROM         StaffHours
WHERE     (SiteID = '164') AND (ID IN
                      (SELECT     Min(ID)
                        FROM          StaffHours
                        WHERE      (SiteID = '164')
                        GROUP BY RecordID
                        HAVING      (COUNT(RecordID) > 1)))
ORDER BY RecordID DESC

