Hi,

I've been trying to wrap my head around this one for a while, and while it would be pretty easy to do using a loop in PHP I'd like to keep it in a single SQL statement if possible.

I have a table of comments, very much like you would store comments in for a blog. It goes something like this:
Code:
idCOMMENT    |     FK_LIST_ID      | COM_Posted | COM_Comment
autoincrement| foreign key of post | Timestamp  | Text for Comment
I only want to keep the most recent 30 or so comments on each listing so I need to create a statement that will delete any excess comments.
I could loop a statement like this in PHP:
Code:
DELETE FROM COMMENT WHERE idCOMMENT IN (SELECT idCOMMENT FROM COMMENT WHERE FK_LIST_ID=(variable from PHP) ORDER BY COM_Posted Desc LIMIT 30, 10^10)
But there will be thousands of listings, (it's and that's going to be rough on the server to make thousands of calls to it each night with the maintenance script. I think there must be a way to do this in pure SQL, just send one SQL statement to the server and that's it, but can't figure it out. Can you point me in the right direction?

Thanks,
John