[thelist] mysql - group by & order by

r937 rudy at r937.com
Sun Dec 7 07:56:42 CST 2008

there is actually another way to do it, which some people find slightly more 
intuitive --

  SELECT t.s_id
       , t.s_group
       , t.s_value
       , t.s_timeStamp
    FROM daTable AS t
   WHERE t.s_timeStamp =
         ( SELECT MAX(s_timestamp)
             FROM daTable
            WHERE s_group = t.s_group )
      BY t.s_timestamp DESC

this approach uses a correlated subquery

basically it says you want the row where the timestamp is the greatest 
timestamp of all rows in the same group

you can see the correlation in the subquery's WHERE clause, referring to the 
"t" column value in the outer query

the problem with this solution is that it often performs worse than the 
derived table solution


More information about the thelist mailing list