Повторяющиеся записи SQL Server, удаляющие самые старые записи и сохраняющие самые новые

Question

У меня есть таблица в SQL Server, похожая на эту:

Emp#        CourseID        DateComplete        Status
1           Course1         21/05/2012          Failed
1           Course1         22/05/2012          Passed
2           Course2         22/05/2012          Passed
3           Course3         22/05/2012          Passed
4           Course1         31/01/2012          Failed
4           Course1         28/02/2012          Passed
4           Course2         28/02/2012          Passed

Попытка записать новейшую запись для каждого курса для каждого emp#. И если тот же курс был предпринят в тот же день, запишите "пройденный" курс.

Думая, что-то вроде этого:

SELECT DISTINCT .....
        INTO Dup_Table
        FROM MainTable
GROUP BY ........
HAVING COUNT(*) > 1

DELETE MainTable
        WHERE Emp# IN (SELECT Emp# FROM Dup_Table)

INSERT MainTable SELECT * FROM Dup_Table

Drop Table Dup_Table
GO

Но не уверен, что это

1. лучший подход и
2. как собрать Emp#/courseID/DateComplete/Status все вместе.

Answer 1

;WITH cte 
     AS (SELECT Row_number() OVER (partition BY EMPID, courseid ORDER BY 
                DateComplete 
                DESC, 
                status DESC) RN 
         FROM   MainTable) 
DELETE FROM cte 
WHERE  RN > 1 

Answer 2

Ты можешь использовать row_number() по разделам и порядку по объему, чтобы получить последнюю запись

Select *
From  (
    Select *,
           Row_Number() Over (Partition By Emp#, CourseID Order By DateComplete DESC, Case When Status = 'Passed' Then 1 Else 2 End  ) AS RecordNumber
    From #Emp)Z
Where Z.RecordNumber = 1