最大値の集計結果に関連データを付加する

質問

1日24回（約1時間毎に）米ドルの為替レートを記録したテーブル（米ドルtbl）があり、
内部に数年分のデータが蓄積されています。

取得日時(datetime)　　米ドル為替レート(usd)
-----------------------------------
2012-10-23 23:00:01　　79.45
2012-10-24 00:00:00　　79.38
2012-10-24 01:00:05　　79.40
2012-10-24 02:00:01　　79.41
　　　　・　　　　　　　　　　　・
　　　　・　　　　　　　　　　　・

このテーブルを使って、各月の米ドルの最大値（最安値）を求めたければ、
select year(datetime) as 年, month(datetime) as 月, max(usd) as 最大値 from 米ドルtbl group by 年, 月;

とすれば、

年　　　月　　最大値
-------------------------
2012 　9　　　79.21
2012 　10　　80.36
2012 　11　　82.76
2012 　12　　84.94
2013 　1　　　91.38
2013 　2　　　94.71
2013 　3　　　96.69

という結果が得られるのはわかるのですが、
この結果に更に、その最大値を取得した日時を加えて、

年　　　月　　最大値　　取得日時
---------------------------------------------
2012　　9　　　79.21　　2012-09-06 15:00:01
2012　　9　　　79.21　　2012-09-13 04:00:00
2012　　9　　　79.21　　2012-09-22 22:00:02
2012　　10　　80.36　　2012-10-24 03:00:00
2012　　11　　82.76　　2012-11-18 22:00:03
2012　　12　　84.94　　2012-12-21 13:00:02
2013　　1　　　91.38　　2013-01-18 20:00:01
2013　　2　　　94.71　　2013-02-14 11:00:00
2013　　3　　　96.69　　2013-03-26 21:00:02

というようなデータを得たいと思うのですが、
どういう構文を書けばいいか、皆目見当がつきません。

その月に最大値の取得日時が何回もあるなら、
上記2012年9月のように、それらすべてが表示されるのが理想です。

どうぞ宜しくお願い致します。

回答No.1

yambejp · ベストアンサー

いまのままのテーブル構成だと集計にあまり向いていないと思いますが
とりあえず動けばいいというならこんな感じでサブクエリにして
joinすればいいのでは？

select 年,月,最大値,`datetime`
from 米ドルtbl
inner join (
select year(`datetime`) as 年, month(`datetime`) as 月, max(usd) as 最大値 from 米ドルtbl group by 年, 月
) as sub on year(`datetime`)=年 and month(`datetime`)=月 and usd=最大値

お礼

早速のご回答ありがとうございます。

さて、頂いた構文を実行したところ、一発で希望通りの結果が得られました。

テーブル（クエリ）をjoinする場合、onする項目はandで複数設定可能なんですね。
恥ずかしながら、初めて知りました。

どうもありがとうございました。