ソートしてからグループ化したいけれど・・・。

質問

SQL構文について質問させて下さい。

ABC │ID │DATE
------------------
XXX │aaa │02/01
YYY │bbb │02/02
YYY │aaa │02/03
と言うデータから、DATEが新しい（大きい）値を抽出し、
次に重複するIDを排除した結果を出したいです。
希望の検索結果は、
ABC │ID │DATE
------------------
YYY │bbb │02/02
YYY │aaa │02/03
となって欲しいのです。
単純にソートした後でIDでグループ化したいのですが、当然できなく、私の考えでは、
SELECT abc, id, max( date ) FROM `test`
GROUP BY id ORDER BY date DESC
なのですが結果は、
ABC │ID │DATE
------------------
YYY │bbb │02/02
XXX │aaa │02/03
と言う風になり、何故か、2行目の様にDATEに対して、ABCの値が別の行の
ID：aaaの値になってしまってます。
どうしたら良いのでしょうか？

回答No.1

yambejp · ベストアンサー

MySQLのバージョンにもよりますがこんな感じで
しょうか。
1度、IDとMAX(`DATE`)を確定させてから、
それをキーに自分自身を検索する感じです。
二回目のORDER BY はダブりがあった場合それを
とるおまじないです。

CREATE TEMPORARY TABLE `tmp` SELECT `ID`,MAX(`DATE`) AS `DATE` FROM `test` GROUP BY `ID`;
SELECT `test`.* FROM `test` INNER JOIN `tmp` on `test`.`DATE`=`tmp`.`DATE` AND `test`.`ID`=`tmp`.`ID` GROUP BY `ID` ORDER BY `DATE` ASC;

お礼

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

なるほど、一時テーブルを作成するのですね。
この方法は思いつきませんでした・・・。
無事、この方法で出来る事を確認しました。
ありがとうございました。

回答No.2

naochancom

バージョンが4.1以降で、サブクエリが
使用OKなら
select a.abc,b.id,b.maxdate
from test a inner join (select id,max(date) as maxdate from test group by id order by date de
sc) b
on (a.id=b.id and a.date=b.maxdate);

これでいけないでしょうか？

お礼

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

すみません。バージョンをご報告してませんでした。
バージョンは4.0を使ってるんです。

私もサブクエリで作ってみたのですが、
まったくできませんでした。
サブクエリは4.1からだったんですね・・・。

とても勉強になりました。