MySql: Delete all rows greater than n entries ordered by datetime

Question

Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?

I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve rows only up to the first 200.

If I have only 199 rows of data, then I need to preserve them all.

Answer 1

This will keep the first 200 rows (and possibly a few more, with identical timestamps):

DELETE t
FROM 
    tableX AS t
  JOIN
    ( SELECT timestampColumn AS ts
      FROM tableX
      ORDER BY ts ASC
      LIMIT 1 OFFSET 199
    ) tlimit
    ON t.timestampColumn > tlimit.ts

---

Additional question: Keep the first 200 rows for every user (by the uid column):

DELETE tt
FROM 
      ( SELECT DISTINCT uid             --- these 3 lines can be replaced
        FROM tableX                     --- with:   UserTable AS du
      ) AS du                           --- a table that you probably have
  JOIN
      tableX AS tt
    ON  tt.uid = du.uid
    AND tt.timestampColumn >
        ( SELECT timestampColumn AS ts
          FROM tableX
          WHERE uid = du.uid 
          ORDER BY ts ASC
          LIMIT 1 OFFSET 199
        )

A (uid, timestampColumn) will be useful with a big table.

Answer 2

If you have an AUTOINCREMENT primary key, and we can assume that you don't update the timestamp, then this should work and be reasonably fast:

DELETE FROM table WHERE id > 
  ( SELECT max(id) FROM 
    ( SELECT id FROM table ORDER BY date DESC LIMIT 200 )
  AS table) ;

Given the assumptions above, ordering by id rather than date would give exactly the same results. I have also assumed you want to delete anything older than the newest 200 so the ORDER BY is DESC.