How to delete all records except the newest MySQL?

Question

PHP

Hello colleagues!
There is a table with user and date columns
Tell me how to delete all records except the 6 most recent (by date) for user=n?

Answer 1

DELETE t
  FROM table1 t
  JOIN (SELECT MIN(date) AS min_date
             FROM (SELECT date
                           FROM table1 
                           WHERE user = n
                           ORDER BY date DESC 
                           LIMIT 6) t1
        ) sel ON t.date < sel.min_date
  WHERE sel.user = n;