Remove all records except for the last 10 per field

Question

I have been trying to find a way to do this but I can't seem to figure out how. The following table is the one I am trying to "clean up".

-------------
| Messages  |
-------------
| ID        |
| user_id   |
| msg       |
| timestamp |
-------------

Basically I want to remove all messages except for the latest 10 per user_id. I really have no idea how to go about this.

Any suggestions would be useful.

There are over 2 million records in this table and I have no use for keeping more than 10 per user. They are never displayed nor will they ever be needed. — Roel, Apr 23 '16 at 10:44

score 2 · Answer 1 · edited May 23 '17 at 10:33

See Using LIMIT within GROUP BY to get N results per group? for how to write a query that finds the newest 10 rows for each user_id. Then you can write a query that joins with that as a subquery, and deletes rows that aren't in it.

DELETE t1.*
FROM YourTable AS t1
LEFT JOIN (SELECT ...) AS t2
ON t1.id = t2.id
WHERE t2.id IS NULL

Replace (SELECT ...) with a query you found in the other question.

Gangaraju · Answer 2 · 2016-04-23T07:53:31.917

-1

You need repeat this for every user.

DELETE FROM Messages where user_id='xyz' and ID NOT IN 
  (SELECT * FROM 
   (SELECT ID FROM Messages where user_id='xyz'  ORDER BY timestamp DESC LIMIT 10)
  AS TOP10)

edited Apr 23 '16 at 07:53

answered Apr 23 '16 at 07:27

Gangaraju

4,406
9
45
77

MySQL doesn't allow `LIMIT` in subqueries that are used in `IN` and `NOT IN`. – Barmar Apr 23 '16 at 07:43
1

having to write a separate query for each user_id is not a very good solution. – Barmar Apr 23 '16 at 07:54

score -2 · Answer 3 · answered Apr 23 '16 at 07:23

-2

try something like this -

delete from messages where ID = (SELECT ID FROM messages
ORDER BY timestamp DESC
LIMIT 10)

I am not sure if this will work, but might give you an Idea of how it can be done. I hope this helps.

answered Apr 23 '16 at 07:23

Rehban Khatri

924
1
7
19

If you use `=`, the subquery has to return a single ID. But your subquery returns 10 IDs. – Barmar Apr 23 '16 at 07:43
This doesn't operate separately on each `user_id`. – Barmar Apr 23 '16 at 07:53

Remove all records except for the last 10 per field

3 Answers3