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 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
You can try to use
JOIN which using a subquery to keep
MAX version each
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
Answered By – D-Shih
Answer Checked By – Robin (BugsFixing Admin)