MYSQL: return value associated with highest number with GROUP BY

Question

In my table I have entries ordered by a timestamp. I want to return a single entry that has the highest timestamp.

This works:

SELECT max(`timestamp`) FROM `messages`

However, it returns just the timestamp value. So I try to select the message value too:

SELECT max(`timestamp`), `message` FROM `messages`

I get this error:

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'database.messages.message'; this is incompatible with sql_mode=only_full_group_by

So I add a GROUP BY:

SELECT max(`timestamp`), `message` FROM `messages`
GROUP BY `message`

However this just ends up returning every value in the table.

I understand GROUP BY is supposed to unify identical entries, I just have absolutely no clue why I'm forced to use it in this situation and how to just get the latest entryrather than all of them.

Answer 1

I want to return a single entry that has the highest timestamp.

Don't aggregate. You can sort and limit instead:

select * from messages order by timestamp desc limit 1