SOSA SOSA - 7 months ago 13
SQL Question

SQL - Are columns always corresponding?

SELECT max(date) as lastdt,name,col1 FROM `consumption` group by name


In the above query, are values of
col1
,
max(date)
and
name
taken from the same row?

Answer

MySQL allows for non-aggregated columns to appear in the SELECT clause of a query. However the values of these columns are indeterminate.

If you want to get the col1 value that corresponds to the record having the maximum date value, then you will have to do an additional join:

SELECT t1.*
FROM `consumption` AS t1
JOIN (SELECT max(`date`) as lastdt, name  
      FROM `consumption` 
      group by name) AS t2
ON t1.name = t2.name AND t1.`date` = t2.lastdt
Comments