Assuming I have a table
foo where I have something like this:
What I want to do is remove all rows that aren't the newest N per user.
The deletion itself could be handled by a:
DELETE FROM foo WHERE id NOT IN (...)
so you could rephrase the problem into: How do I get the newest N(there might be less) rows for each user. This means if I have U users I may end up with N*U rows so
LIMIT wont really work.
MySQL does not support reading from a table with
SELECT and performing an
DELETE on the same table in the same query. So doing what you want in one statement is going to be tricky.
I would do it in two stages: first, query the newest
$N records per user, and store them in a temporary table:
CREATE TEMPORARY TABLE foo_top_n SELECT f1.id FROM foo f1 LEFT OUTER JOIN foo f2 ON (f1.user_id = f2.user_id AND f1.id < f2.id) GROUP BY f1.id HAVING COUNT(*) < $N;
Next, use the multi-table
DELETE syntax and join
foo to the temporary table, deleting where no match is found:
DELETE f1 FROM foo f1 LEFT OUTER JOIN foo_top_n f2 USING (id) WHERE f2.id IS NULL;