Delete old records (without date field)

[techtheatre]

I have a list of entries in my database where I always only use the most recent 100 entries. I successfully accomplish this with:

SELECT * FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100;

In order to begin cleaning up the data, is there a command that will delete the records beyond the initial 100? I can't just do

DELETE FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100;

because that will delete my first 100 records...i want record 101+ (from the queried results of course...not the whole table).

Also, i do not have a date field, so this is not deletion based upon a date range...just based upon result number.

THANKS!

[dreamcatcher]

CREATE table2 (SELECT * FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100);
RENAME TABLE table TO old_table,table2 TO table;
DROP TABLE old_table;

dc

[techtheatre]

I am looking for something I can set up in a cron job (or other) to automatically clean out older records periodically...so it needs to keep the same table and structure, just with some rows deleted.

[topr8]

dreamcatcher - if there were various permissions on the original table i think the newly renamed table won't have them ... i'm happy to stand corrected.

techtheatre - you could try this

DELETE FROM table WHERE field NOT IN (SELECT field FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100);

[techtheatre]

I think that should work. It will take some tweaking to get it right, but i think this is absolutely what i was looking for. THANKS!

[dreamcatcher]

dreamcatcher - if there were various permissions on the original table i think the newly renamed table won't have them ... i'm happy to stand corrected.

Nope, you may well be correct.