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

Expected result:

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

CodePudding user response：

First, define which records you would like to preserve, e.g. for the maximal RN per DN

SELECT dn, max(rn) rn_max from demo11 group by dn

Then, exclude these records from the deletion:

DELETE FROM demo11 d   
    LEFT JOIN ( SELECT dn, max(rn) rn_max from demo11 group by dn ) tmp
    ON d.dn = tmp.dn AND d.rn = tmp.rn_max
    WHERE tmp.dn is null

CodePudding user response：

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

CodePudding user response：

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

It doesn't work like that, even just in basic terms of sets of data. Here's a simpler example:

John, Chicago
John, New York
Jane, Chicago

I want all rows that aren't John from Chicago. This means I want John/NewYork and Jane/Chicago.

If I say name <> 'John' and city <> 'Chicago' I exclude everything. Jane would have been kept because her name isn't John, but her city is Chicago, which isn't "not Chicago". This is the same as saying name NOT IN ('John') and city NOT IN ('Chicago') - the core problem here is that you're trying to exclude "John from Chicago" as a pair of values, but these two clauses (1: name <> 'John', 2:city <> 'Chicago') do not work together - the do not keep the values together. Some rows are excluded because they are John, other rows are excluded because they are Chicago - there isn't any notion of a pair of values John Chicago with this

What was actually wanted was NOT(name = 'John' AND city = 'Chicago'). In this, the data remains paired up. John/Chicago stay together as a clause and the NOT operates on the pair of them. This is very different to some rows being excluded because of one thing and other rows being excluded for another thing

Some databases allow multiple values in a NOT IN, so they would also permit a construct like:

(name, city) NOT IN ('John, 'Chicago')

The pairing of John/Chicago are again kept together and work in unison to exclude just one row where the values are John/Chicago as a pair

Other answers have given you the SQL you need to use, but I wanted to talk about why your try didn't work. You need to appreciate that on their own, your two truth statements DN NOT in (1,2,3) and NOT IN ('A-3','C','A-4') work separately and affect different rows. There isn't anything at all that keeps the values inside the two INs paired up together.. If your database had:

(DN, RN) NOT IN ( (1,'A-3'), (2,'C'), (3,'A-4') )

Then it would have worked out, because those DN/RN pairs are kept together. If you don't have a database that works like that, then you have to use something more like:

NOT (
  (DN=1 AND RN='A-3') OR (DN=2 AND RN='C') OR (DN=3 AND RN='A-4')
)

These also keep the data pairs you're looking for "together" in that it's this and that or this2 and that2 or this3 and that3 (not)