- Edited
This exercise is much more complex then one would think...
So far it seems difficult to be solved in a simple and elegant way
Here are two solutions to this problem:
http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by
My solution below is inspired by this page:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
@arithma, as far as MySQL is concerned, your query is missing a comma after `name` on the first line. And BTW I think your query is the most elegant. Still not necessarily easy to understand.
So far it seems difficult to be solved in a simple and elegant way
Here are two solutions to this problem:
http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by
My solution below is inspired by this page:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
SELECT
`r1`.`id`,
`currency`.`name`,
`r1`.`rate`
FROM `usd_rate` AS `r1`
LEFT JOIN `currency`
ON `r1`.`currency_id` = `currency`.`id`
WHERE
`r1`.`stamp` = (
SELECT
MAX(`stamp`)
FROM `usd_rate` AS `r2`
WHERE
`r1`.`currency_id` = `r2`.`currency_id`
AND `r2`.`stamp` <= '2012-04-28 20:26:04'
)
Not exactly sure how... but I somehow managed to make it work :)@arithma, as far as MySQL is concerned, your query is missing a comma after `name` on the first line. And BTW I think your query is the most elegant. Still not necessarily easy to understand.