Delete all rows except the one with the biggest value

Question

I store transactions in a table, and I want to delete all transactions (grouped by a user_id) except the one with the biggest amount, here is an example table:

+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
| 1  | 1       | 10     |
+----+---------+--------+
| 2  | 1       | 20     |
+----+---------+--------+
| 3  | 1       | 30     |
+----+---------+--------+
| 4  | 2       | 50     |
+----+---------+--------+
| 5  | 2       | 100    |
+----+---------+--------+
| 6  | 3       | 2      |
+----+---------+--------+
| 7  | 3       | 4      |
+----+---------+--------+

I want the following result

+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
| 3  | 1       | 30     |
+----+---------+--------+
| 5  | 2       | 100    |
+----+---------+--------+
| 7  | 3       | 4      |
+----+---------+--------+

I tried

DELETE FROM `transactions`
WHERE `user_id` NOT IN (
  SELECT `user_id`
  FROM (
    SELECT MAX(`amount`) AS ts
    FROM `transactions` e
    WHERE `user_id` = `user_id`
  ) s
  WHERE ts = `transactions`.`amount`
)
ORDER BY `transactions`.`user_id` ASC

Possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) — ESP32, Apr 08 '16 at 16:14
Perhaps update with a deleted flag rather than removing them. — Alex K., Apr 08 '16 at 16:15
@Gerfried i don't want to delete duplicate values, i want to delete all values except the bigger amount example: a user have 4 transactions, 1- 10$ 2- 20$ 3- 40$ i want to delete 1 and 2 and keep the 40$ — Spoody, Apr 08 '16 at 16:31

score 4 · Accepted Answer · answered Apr 08 '16 at 16:14

4

DELETE FROM `transactions`
WHERE id NOT IN 
(
    SELECT MAX(id)
    FROM `transactions`
    group by user_id
)

The inner query groups by each user and select only the highest ID for each. Delete all records except the IDs from the inner select.

answered Apr 08 '16 at 16:14

juergen d

201,996
37
293
362

score 1 · Answer 2 · answered Apr 08 '16 at 16:15

Wasn't sure what did you mean by except the latest one so I considered except last record inserted hence ORDER BY id DESC was used

DELETE FROM `transactions`
WHERE `id` NOT IN (
    SELECT `id`
    FROM `transactions`
    GROUP BY `user_id`
    ORDER BY `id` DESC
)

Delete all rows except the one with the biggest value

2 Answers2