Selectively delete mostly duplicate records from MySQL

Question

I have a table:

PRICE_UPDATE id (int 5, auto-increment, primary, unique) part_number (varchar 10, non-null) price (float(10,2), non-null) 

Some of part_number duplicated (1 or more duplicate entries). Sometimes with the same price, sometimes with different prices.

How to remove all duplicate rows based on part_number, leaving either the highest price or only 1 entry if the prices were the same?

Is it even doable in direct MySQL?

Answer 1

 DELETE t1 FROM YourTable t1, YourTable t2 WHERE t1.part_number = t2.part_number AND (t1.price, t1.id) < (t2.price, t2.id) 

Answer 2

Inside, outside:

• Selects identifiers with a maximum price for package_number
• Selects the maximum id with the maximum price per batch_packages
• Deletes identifiers that are not in 2.

---

 delete tablename where id not in ( (select max(id) from tablename a inner join ( select id, max(price) from tablename group by part_number ) b on a.id = b.id and a.price = b.price group by part_number))