How to find duplicate entries in MySQL then delete all but the highest subvalue-CodePudding

I kind of understand how to find duplicates but I need to delete all but the one with the highest sub value in a separate row.

 ------------ --------------- 
| ID         | Expiration    |
 ------------ --------------- 
| 828044 | 5020581708 |
| 586536 | 1672700156 |
| 586536 | 1672700259 |
| 586536 | 1672700300 |
| 828044 | 1672700400 |
 ------------ ---------------

Again I want to sort by the ID then save the one with the highest Expiration and delete the rest. Table looks like that. Any thoughts?

CodePudding user response：

You do a self join to find rows where there's another row that would be kept instead:

delete a
from Access as a
join Access as b on b.ID=a.ID and b.Expiration > a.Expiration

This will benefit from an index on (ID,Expiration).