d_barszczak Posted February 13, 2009 Share Posted February 13, 2009 Hi all, I am developing a chat application which stores messages in a database. I don't need to keep any more than 100 lines so i will need to run a query that deletes all rows apart from the last 100 submitted. id = message id room = room id submit_time = Submitted time msg = message SELECT * FROM post_table WHERE `room` = '1' limit last 100 Quote Link to comment https://forums.phpfreaks.com/topic/145057-delete-all-rows-apart-from-last-100/ Share on other sites More sharing options...
dreamwest Posted February 13, 2009 Share Posted February 13, 2009 One way is to get the number of rows, then minus 100 then limit deletion submit_time ASC Thatll leave 100 newest rows Quote Link to comment https://forums.phpfreaks.com/topic/145057-delete-all-rows-apart-from-last-100/#findComment-761166 Share on other sites More sharing options...
d_barszczak Posted February 13, 2009 Author Share Posted February 13, 2009 OK thanks, was hoping there would be a simple query Quote Link to comment https://forums.phpfreaks.com/topic/145057-delete-all-rows-apart-from-last-100/#findComment-761168 Share on other sites More sharing options...
Mchl Posted February 13, 2009 Share Posted February 13, 2009 Try: DELETE FROM post_table WHERE id NOT IN(SELECT id FROM post_table ORDER BY submit_time DESC LIMIT 100) Quote Link to comment https://forums.phpfreaks.com/topic/145057-delete-all-rows-apart-from-last-100/#findComment-761175 Share on other sites More sharing options...
fenway Posted February 15, 2009 Share Posted February 15, 2009 LEFT JOIN ... IS NULL might be faster. Quote Link to comment https://forums.phpfreaks.com/topic/145057-delete-all-rows-apart-from-last-100/#findComment-762699 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.