Unable to delete specific records using temp tables and using not in operator-CodePudding

create table demo11 (dn int, rn varchar(max))
insert into demo11 values(1,'A'),(1,'A-1'),(1,'A-3'),(2,'A'),(2,'B'),(2,'C'),(3,'A-3'),(3,'A-4'),(4,'A'),(4,'A-1'),(4,'A-2'),(4,'A-5')
    
    DROP TABLE #dnrn
    CREATE TABLE #dnrn(dn int,rn VARCHAR(MAX))
    insert into #dnrn(dn,rn)
    SELECT dn AS dn, MAX(rn) AS rn FROM demo11 GROUP BY dn
    SELECT * FROM #dnrn 
    delete from demo11 where dn NOT IN (SELECT dn FROM #dnrn) AND rn NOT IN (SELECT rn FROM #dnrn)
    SELECT * FROM demo11

Tried with above query but didnot got desired result

Expected result : demo11 should consists of only max revision record group by dn

Output should be : (1 A-3), (2 C), (3 A-4), (4, A-5)

CodePudding user response：

You have each dn from demo11 in the temporary table, so dn NOT IN (SELECT dn FROM #dnrn) is false for every row in demo11 and thus no row gets deleted.

You can achieve what you want to do when you join a derived table (there's no need for a temporary table) which uses the row_number() window function to a assign numbers for the values of rn per dn value and then filer for all rows where this number isn't 1, which designates the maximum.

DELETE d
       FROM demo11 AS d
            INNER JOIN (SELECT dn,
                               rn,
                               row_number() OVER (PARTITION BY dn
                                                  ORDER BY rn DESC) r
                               FROM demo11) AS x
                       ON d.dn = x.dn
                          AND d.rn = x.rn
       WHERE x.r <> 1;