sqlite How to add another subqueries?

user_12741325 0
Viewed 51

I have in sqlite query: 

   DELETE FROM test
      WHERE a NOT IN (
            SELECT * FROM (
                   SELECT MAX(a) FROM test GROUP BY z )
                     )
It is working ok. Problem begins if there is another line with the same 'a' max value. In this case none of lines are deleted. How to add another condition (subqueries) - when are two the same max values, number from column b determines what of line should be deleted ?

sqlite
1 Answers

If you want to keep the rows with the max a for each z and delete the others then you can use EXISTS:

DELETE FROM test
WHERE EXISTS (
  SELECT 1 FROM test t
  WHERE t.z = test.z AND a > test.a
)

Or with a correlated subquery:

DELETE FROM test
WHERE a < (SELECT MAX(t.a) FROM test t WHERE t.z = test.z)

Or by using ROW VALUES with the operator IN:

DELETE FROM test
WHERE (z, a) NOT IN (SELECT z, MAX(a) FROM test GROUP BY z)

Edit:

If you have another column b that resolves the ties in case there are more than 1 rows with the same max a then use also ROW_NUMBER() or RANK() window function:

DELETE FROM test
WHERE (z, a, b) IN (
  SELECT z, a, b
  FROM (
    SELECT z, a, b, ROW_NUMBER() OVER (PARTITION BY z ORDER BY a DESC, b DESC) rn
    FROM test
  ) t
  WHERE rn > 1  
)

Or delete all the rows with rowid different than the rowid with the max a (and max b if there are ties) for each z:

DELETE FROM test
WHERE rowid <> (SELECT t.rowid FROM test t WHERE t.z = test.z ORDER BY t.a DESC, t.b DESC LIMIT 1)

Or with EXISTS:

DELETE FROM test
WHERE EXISTS (
  SELECT 1 FROM test t
  WHERE (t.z = test.z) AND (t.a > test.a OR (t.a = test.a AND t.b > test.b))
);
user_104988280
Related