我的sql select语句结果不正确

Question

I have small mysql table with:

id name status class desk date username

It has around 300 records.

When selecting data to get the latest record by date (formatted), along with username, I use this statement:

SELECT DATE_FORMAT(MAX(date), '%d-%m-%Y at %H:%i'), username as LatestRecord from myTable

It returns the correct record for dateTime but gives different username which is not in the same selected row. It seems like it doesn't select the username for the same resulted lastest date/time.

Any idea why this happens?

Answer 1

If you want the latest record then you should add an ORDER BY and a LIMIT clausule's to your query:

SELECT DATE_FORMAT(date, '%d-%m-%Y at %H:%i') as LatestDate, username as LatestRecord 
FROM myTable
ORDER BY date DESC
LIMIT 1