[thelist] mysql - group by & order by

[r937]

> SELECT s_id, s_group, s_value, s_timeStamp FROM tbl
> GROUP BY s_group
> ORDER BY s_timeStamp DESC
>
> unfortunately, this applies the GROUP BY before the ORDER BY,
> so returns the first item from each group

actually, it isn't the first item from each group (since rows in a database
table have no sequence), it could be any one of them

mysql explains this here:
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

  "Do ~not~ use this feature if the columns you omit from the
   GROUP BY part are not constant in the group. The server is
   free to return any value from the group, so the results are
   indeterminate unless all values are the same."

the way to solve this is to get a derived table which tells you the max
timestamp for each group, and join that back to the table

  SELECT t.s_id
       , t.s_group
       , t.s_value
       , t.s_timeStamp
    FROM daTable AS t
  INNER
    JOIN ( SELECT s_group
                , MAX(s_timestamp) AS maxtime
             FROM daTable
           GROUP
               BY s_group ) AS m
      ON m.s_group = t.s_group
     AND m.maxtime = t.s_timestamp
  ORDER
      BY t.s_timestamp DESC


p.s. GROUP BY is always processed before ORDER BY, not because of their
sequence in the query statement, but because the sequence of execution is
like this:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

;o)

rudy
http://r937.com/