Delete logical duplicates using Ranking Function

A simple way to delete duplicate (not absolute duplicate records where records are completely identical) records from table using the ranking function and CTE is as follows.

The following SQL will remove logical duplicate records from the table tblTransaction.

If the table tblTransaction has multiple records for a combination of AccountId and RecordId the DELETE syntax will keep only the latest dates and delete all previous records for the combination.

WITH Transaction_CTE (Ranking, AccountId, RecordId, StartDate) AS (

SELECT Ranking = DENSE_RANK()

OVER(PARTITION BY AccountId, RecordId

ORDER BY StartDate ASC),

AccountId, RecordId, StartDate

FROM dbo.tblTransaction )

DELETE FROM Transaction_CTE WHERE Ranking > 1