SQL DELETE - Only keep last n records in group (delete old records in group)-CodePudding

[Edit] : this answer helped

Or use this solution if you do not have a primary key. You can calculate one with your composite key columns and do the same idea as above. Just keep in mind the numerical datatypes will need to be converted to nvarchar() when there are NVARCHAR columns involved.

    SELECT --Before Delete
        CAST(T.[status_id] AS NVARCHAR(1)) T.ref_entity_aa CAST(T.ref_entity_ab AS NVARCHAR(1)) T.[status]
        ,* FROM #TEMP T
    
    DELETE #TEMP WHERE CAST([status_id] AS NVARCHAR(1)) ref_entity_aa CAST(ref_entity_ab AS NVARCHAR(1)) [status] NOT IN (
    SELECT CAST([status_id] AS NVARCHAR(1)) ref_entity_aa CAST(ref_entity_ab AS NVARCHAR(1)) [status] 
    FROM(
    SELECT status_id
        ,RANK() OVER(PARTITION BY ref_entity_aa,ref_entity_ab,[status] ORDER BY status_id DESC) [rank]
        ,ref_entity_aa
        ,ref_entity_ab
        ,[status]
    FROM #TEMP
    )A
    WHERE [rank] <= 1 --'N'
    )
    
SELECT --After Delete
    CAST([status_id] AS NVARCHAR(1)) ref_entity_aa CAST(ref_entity_ab AS NVARCHAR(1)) [status]
    ,T.ref_entity_aa
    ,T.ref_entity_ab
    ,T.[status]
FROM #TEMP t

OUTPUT WITH CONCATENATED KEY:(BEFORE AND AFTER)