[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 )
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
More information about the thelist
mailing list