[SOLVED] MySQL – Delete duplicate records but keeps XX records

Issue

My problem is that I want to delete duplicated records but keeps XX latest records. For example:

id ean price price_type country valid_to
1 12345678 19.99 b2c US 2022-03-30
2 12345678 18.99 b2c US 2022-03-28
3 12345678 17.99 b2c US 2022-03-26
4 11122233 146.99 b2b US 2022-03-30
5 11122233 150.99 b2b US 2022-03-28
6 11122233 170.99 b2b US 2022-03-26
7 11122233 180.99 b2b US 2022-04-01

Desired results – keeps 2 latest records:

id ean price price_type country valid_to
1 12345678 19.99 b2c US 2022-03-30
2 12345678 18.99 b2c US 2022-03-28
4 11122233 146.99 b2b US 2022-03-30
7 11122233 180.99 b2b US 2022-04-01

Which query statement should I make to do this? Thank you!

Solution

What is precise MySQL version? – Akina

MySQL 5.7 – nphuly

DELETE t0
FROM test t0
JOIN ( SELECT t1.id, COUNT(*) cnt
       FROM test t1
       JOIN test t2 ON t1.ean = t2.ean AND t1.valid_to <= t2.valid_to
       GROUP BY t1.id ) t3 ON t0.id = t3.id
WHERE t3.cnt > 2

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3284d0a012272813c1fbd6121bfd51b3

Answered By – Akina

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.