I want to delete all the rows (for each user) that are older than the 30 newest records.
For example there are three users bob, jack, and jim. They each have 100 records. I want to delete the all except the newest 30 records.
I could effectively do something like this for each user:
Code: Select all
DELETE FROM `table` WHERE `id` NOT IN (SELECT `id` FROM `table` WHERE `username` = 'bob' ORDER BY `id` DESC LIMIT 0, 30) AND `username` = 'bob'I was thinking I could do a group by `username` somewhere, but I'm not sure how to do it using subqueries.
Is this possible in one query?