delete query is not working when both columns are selected

Question

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')

dn	rn
1	A
1	A-1
1	A-3
2	A
2	B
2	C
3	A-3
3	A-4

Expected result:

dn	rn
1	A-3
2	C
3	A-4

Tried with following query but not worked:

delete from demo11 
where DN NOT in (1,2,3) AND RN NOT IN ('A-3','C','A-4') 

Answer 1

I think you don't need to delete, just select by using a window function such as ROW_NUMBER()

SELECT dn, rn
  FROM
  (
   SELECT ROW_NUMBER() OVER (PARTITION BY dn ORDER BY rn DESC) AS rnum,
          d.* 
     FROM demo11 AS d 
  ) AS dd
 WHERE rnum = 1 

If you insist on deleting those values, then use the window function again such as

WITH t AS
(
 SELECT dn AS dn2, rn AS rn2
   FROM
   (
    SELECT ROW_NUMBER() OVER (PARTITION BY dn ORDER BY rn DESC) AS rnum,
           d.* 
      FROM demo11 AS d 
   ) AS dd
  WHERE rnum > 1 
)
DELETE 
  FROM demo11
 WHERE EXISTS ( SELECT 1 FROM t WHERE rn=rn2 AND dn=dn2)

Demo