• Coding
  • [Exercise] SQL - Record history

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
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.
@rolf: thanks, corrected.
I like your solution, and now its equivalent to Ayman's.

For the better or worse, my query will return just one item if there are multiple entries for each currency at the same timestamp (which should be an alarming thing anyway). Your query will return them all.

Hope you all enjoyed the exercise. Like to have more?
arithma wroteHope you all enjoyed the exercise. Like to have more?
Yep :)