-1

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?

Ace
  • 13
  • 5
  • Does this answer your question? [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) – kmoser Jan 01 '23 at 23:18
  • the suggested duplicate doesn't have answers that fit this question – ysth Jan 01 '23 at 23:20
  • Still don't get how to pick the higher Expiration value from the ID's and delete the rest – Ace Jan 01 '23 at 23:22

1 Answers1

0

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).

ysth
  • 96,171
  • 6
  • 121
  • 214
  • This kind of makes sense but I am pretty new to MySQL. Could you explain how to put this into a command line so I know how to format it in my script? My table name is Access and I'm not sure what to put in place of the a and b you put. – Ace Jan 01 '23 at 23:28
  • I have been learning by using things like this: `INSERT INTO Access VALUES ('828044 ', '1672700259 ');` Then incorporating that into my normal code. – Ace Jan 01 '23 at 23:29
  • you can use a and b or anything else; `tablename as foo` means use tablename but refer to it as foo everywhere else in this query; when you use the same table twice in a query you need to give an alias for at least one of them. – ysth Jan 01 '23 at 23:33
  • not sue what you mean about formatting? it's all just one query, the line breaks just add clarity – ysth Jan 01 '23 at 23:34
  • I guess I just typed something wrong then, thank you helped a lot. – Ace Jan 01 '23 at 23:37