I have this query:

SELECT 
    T1.ID_NUMBER,                                                                    
    T1.INCEPTION_DATE,
    T1.OCCURENCE,
    T1.TRANSACTION_DATE,
    T1.FILE_LOAD_DATE,
    T1.BATCH_NUM
FROM 
    mastertable T1
INNER JOIN 
    (SELECT 
         ID_NUMBER, INCEPTION_DATE, OCCURENCE, 
         COUNT(*) AS DUPL_COUNT
     FROM 
         mastertable
     WHERE 
         SOURCE_SYSTEM ='LEGACY'
     GROUP BY 
         ID_NUMBER, INCEPTION_DATE, OCCURENCE
     HAVING 
         COUNT(*) > 1) t2 ON T2.ID_NUMBER = T1.ID_NUMBER 
                          AND T2.INCEPTION_DATE = T1.INCEPTION_DATE 
                          AND T2.OCCURENCE= T1.OCCURENCE
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)

CodePudding user response：

You could use a deletable CTE here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID_NUMBER, INCEPTION_DATE, OCCURENCE
                                 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 OCCURENCE. 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 breaker will be determined by the earliest BATCH_NUM.

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