[Exercise] SQL - Record history

SELECT 
  `currency`.`id` AS `id`,
  `currency`.`name` AS `name`,
  `usd_rate`.`rate` as `rate`
FROM `currency` 
LEFT JOIN 
  `usd_rate` ON `currency`.`id` = `usd_rate`.`id` 
WHERE 
  `usd_rate`.`stamp` < '2009-10-14 1300GMT' 
ORDER BY `usd_rate`.`stamp` DESC 
LIMIT 1

-- --------------------------------------------------------
-- Host:                         localhost
-- Server version:               5.5.15-log - MySQL Community Server (GPL)
-- Server OS:                    Win32
-- HeidiSQL version:             7.0.0.4053
-- Date/time:                    2012-04-27 13:18:21
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

-- Dumping database structure for test
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;


-- Dumping structure for table test.currency
CREATE TABLE IF NOT EXISTS `currency` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table test.currency: ~0 rows (approximately)
/*!40000 ALTER TABLE `currency` DISABLE KEYS */;
/*!40000 ALTER TABLE `currency` ENABLE KEYS */;


-- Dumping structure for table test.usd_rate
CREATE TABLE IF NOT EXISTS `usd_rate` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `currency_id` int(10) NOT NULL,
  `rate` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_usd_rate_currency` (`currency_id`),
  CONSTRAINT `FK_usd_rate_currency` FOREIGN KEY (`currency_id`) REFERENCES `currency` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table test.usd_rate: ~0 rows (approximately)
/*!40000 ALTER TABLE `usd_rate` DISABLE KEYS */;
/*!40000 ALTER TABLE `usd_rate` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

SELECT Currency.Name, usd_rate.Stamp, usd_rate.Rate
FROM Currency, usd_rate
WHERE usd_rate.Stamp =  '2012-04-27 00:00:00'
AND Currency.ID = usd_rate.Currency_ID

arithma wroteJust to make sure you guys don't miss what I meant: In the result, every currency must at most be represented once. You should have considered this by yourselves as a plausible scenario and picked to solve against it. At any rate...

SELECT 
  `currency`.`id` AS `id`,
  `currency`.`name` AS `name`,
  `usd_rate`.`rate` as `rate`
FROM `currency`
LEFT JOIN 
  `usd_rate` ON `currency`.`id` = `usd_rate`.`id` 
WHERE 
  `usd_rate`.`stamp` < '2009-10-14 13:00:00' 
GROUP BY `currency`.`id`

SELECT *
FROM usd_rate t
INNER JOIN
(
    SELECT usd_rate.Currency_ID, max(usd_rate.Stamp) AS maxStamp
    FROM   usd_rate
    GROUP BY Currency_ID
) AS m
ON t.Currency_ID = m.Currency_ID
AND t.Stamp = m.maxStamp

AymanFarhat wrote@Arithma yes you're right this would only work if the time instance is the current timestamp. In case the timestamp was at a prior point in time(less than the real max) the max value should be derived relative to the input timestamp.