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.

A simple way to solve non aggregated columns: https://stackoverflow.com/a/40578865/439171 — Italo Borssatto, Mar 07 '20 at 18:31

score 5 · Accepted Answer · answered Jan 10 '20 at 11:46

5

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

answered Jan 10 '20 at 11:46

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Jan 10 '20 at 12:07

0

Please try with below mysql query. first inner query get the highest number. after that outer query remove data repetition.

SELECT 
 * 
FROM
  (SELECT 
    `timestamp` 
  FROM
    `messages` 
  ORDER BY `timestamp`) 
GROUP BY `timestamp`

answered Jan 10 '20 at 12:07

Harendra Singh

203
4
13

MYSQL: return value associated with highest number with GROUP BY

2 Answers2