Trying to delete duplicate rows in MSSQL where the difference is the date or batch number

I have this query:

SELECT T1.ID_NUMBER,                                                                    

  T1.INCEPTION_DATE,

  T1.OCCURANCE,

  T1.TRANSACTION_DATE,

  T1.FILE_LOAD_DATE,

  T1.BATCH_NUM

FROM mastertable T1

INNER JOIN (SELECT ID_NUMBER,INCEPTION_DATE,OCCURANCE, COUNT(*) AS DUPL_COUNT

    FROM mastertable

    WHERE SOURCE_SYSTEM ='LEGACY'

    GROUP BY ID_NUMBER,INCEPTION_DATE,OCCURANCE

    HAVING COUNT(*) > 1

    ) t2 ON T2.ID_NUMBER = T1.ID_NUMBER AND T2.INCEPTION_DATE = T1.INCEPTION_DATE AND T2.OCCURANCE = T1.OCCURANCE 

ORDER BY 1,2,3,4,5

Which is returning the following results

The earlier FILE_LOAD_DATE of each ID_NUMBER or lesser BATCH_NUM is the record I want to keep.

Is there a way to write a query that will delete the other records, perhaps using a CTE with ROW_NUMBER()?

I am hoping for something that is DRY in case this issue happens again. Thanks!

(Also if it isn’t too much trouble please explain how the solution works)

>Solution :

You could use a deletable CTE here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID_NUMBER, INCEPTION_DATE, OCCURANCE
                                 ORDER BY FILE_LOAD_DATE, BATCH_NUM) rn
    FROM mastertable
    WHERE SOURCE_SYSTEM = 'LEGACY'
)

DELETE
FROM cte
WHERE rn > 1;

The logic here is to assign a row number to each group of records having the same values for ID_NUMBER, INCEPTION_DATE, and OCCURANCE. The first row number value of 1 will be assigned to the record having the earliest FILE_LOAD_DATE. In cases of two or more records tied for the earliest FILE_LOAD_DATE, the tie will be broken by the earliest BATCH_NUM.

The delete statement removes all records except for this earliest record.