How to delete last record(on condition) from a table in MySql

Question

I have a LoginTime table like this:

id | user_id | datetime
1  |   1     | 2011-01-17 18:51:05
2  |   1     | 2011-01-18 18:51:05  
3  |   1     | 2011-01-19 18:51:05  
4  |   2     | 2011-01-19 18:51:05  

I want to delete last record for user_id=1. Last record of a user can be recognized by datetime.

How can I do this with one query.

Answer 1

You need to filter the table by user_id (eg WHERE user_id=1), then sort it by time (eg ORDER BY datetime) and then limit the query to just one item (eg LIMIT 1) and you delete the result of this query. At the end youl get query like this:

DELETE FROM LoginTime WHERE user_id=1 ORDER BY datetime DESC LIMIT 1