CODEWITHSUNDEEP
mysqlgroup-by
blackswan
blackswan

Reputation: 204

mysql group by not returning correct corresponding rows

The following query returns the correct latest_time but the corresponding fields are not the correct ones. How do I get matching fields for the given MAX value?

select `id`,`value1`,`value2`, MAX(`timestamp`) as `latest_time` 
   from `table` 
   group by `value1`

Upvotes: 0

Views: 60

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Non-aggregated columns that appear in the SELECT have an indeterminate value. You need a self-join to get the required values:

select t1.`id`, t1.`value1`, t1.`value2`, t1.`timestamp`
from `table` as t1
join (
   select `value`, MAX(`timestamp`) as `latest_time` 
   from `table` 
   group by `value1`
) as t2 on t1.`value1` = t2.`value1` and t1.`timestamp` = t2.`latest_time`

Upvotes: 1

Related Questions