[thelist] mysql - group by & order by

[r937]

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 )
  ORDER
      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


rudy