How to delete all rows over 50 count?

My table:

_id | state | timeStamp

I want select all the rows that are of state=0 and order that result by timeStamp. I than want to delete all items passed a particular limit, say 50 rows.

*Essentially, I don't want there to be more than 50 rows with the state=0

How can I achieve this? I tried writing one but I'm gettin开发者_JAVA技巧g a bit lost...

DELETE FROM table WHERE (state=0) ORDER BY timeStamp

Use the NOT IN statement with a sub query:
DELETE FROM table WHERE state = 0 AND _id NOT IN(SELECT _id FROM table WHERE state = 0 ORDER BY timeStamp LIMIT 50);

What it does is select all rows where state = 0 and then removes the 50 first rows that has state = 0 all while ordering on timeStamp

You can find what the time stamp is for the 50th row and then do a delete statement for rows whose state = 0 and whose timestamp is older (or newer, whichever applies here) than the time stamp you found.