CODEWITHSUNDEEP
sqlsql-serverdateduplicates
user1407577
user1407577

Reputation: 83

SQL Server Duplicate Records deleting oldest record and keeping newest

I have a table in SQL Server similar to this:

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

Trying to capture the newest record for each course for each emp#. And if the same course has been attempted on the same day capture the 'passed' course record.

Thinking something along these lines:

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

But not sure if this is the

  1. the best approach and
  2. how to bring the Emp#/courseID/DateComplete/Status all together.

Upvotes: 1

Views: 9463

Answers (2)

mehdi lotfi
mehdi lotfi

Reputation: 11571

You can use row_number() by partition and order by scope to get last record

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

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

;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

Upvotes: 8

Related Questions