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.