How to delete all dulicate table excluding max value?

March 9, 2022

There are a ton of questions and answeres out there regarding this question, but none is working for my special problem:

I have a table parts with columns Number and Version.

Number Version
A123   0
A123   1
A123   2
B123   0
C213   0
C123   1
...    ...

I want to remove all duplicate Numbers but keep the ones with the max Version.

I created this code:

DELETE FROM `parts`
WHERE Version NOT IN
    (SELECT * FROM 
        (
            SELECT MAX(Version)
            FROM `parts`
            GROUP BY Number
        ) AS duplicates
);

But this has a unexpected behaviour and is not removing all duplicates.

Here is a fiddle: http://sqlfiddle.com/#!9/4cc59e0/1

>Solution :

You can try to use DELETE with JOIN which using a subquery to keep MAX version each Number

DELETE p1
FROM parts p1
INNER JOIN
(
  SELECT MAX(pp.Version) m_version,pp.Number
  FROM parts pp
  GROUP BY pp.Number
) p2 ON p2.Number = p1.Number AND m_version > p1.Version

sqlfiddle