GROUP BYで集約されるときのソートを変えたい

質問

GROUP BYで集約されるときのソートを変えたい

MySQLの5.1を使用しています。
テーブル"tbl"には、"no","id","score"の3つのフィールドがあり、
"no"は主キーです。
+---+----+-------+
| no　|　id　|　score　|
+---+----+-------+
|　1　|　10　|　10002　|
|　2　|　10　|　10000　|
|　3　|　10　|　10008　|
|　4　|　11　|　10004　|
|　5　|　12　|　10006　|
+---+----+-------+

上記の表から、
scoreの値が高い順にグループ化してソートしたいのですが、
以下のSQL文ではscoreが最初に登録されたものに集約されてからグループ化されてソートしてしまうため、
思い通りの結果が得られずに困っています。

↓具体例
SELECT no, id, score
FROM tbl
ORDER BY score DESC
GROUP BY id;
+---+----+-------+
| no　|　id　|　score　|
+---+----+-------+
|　5　|　12　|　10006　|
|　4　|　11　|　10004　|
|　1　|　10　|　10002　|
+---+----+-------+

↓欲しいソート順
+---+----+-------+
| no　|　id　|　score　|
+---+----+-------+
|　3　|　10　|　10008　|
|　5　|　12　|　10006　|
|　4　|　11　|　10004　|
+---+----+-------+

テンポラリテーブルはなるべく使用せずソートしたいのですが、
このような事は可能なのでしょうか？
ご存知の方がいらっしゃいましたら教えていただけると助かります。
よろしくお願いします。

回答No.1

chukenkenkou · ベストアンサー

まず、一般的なSQLとして、間違った使い方をしています。

group by指定時、select句に指定できるのは、

(1)group byで指定した列
(2)count、max、minなどの集約（集計、集合とも呼ばれる）関数
(3)定数

だけです。

group byで1個以上の行を集約して1行で返す訳ですから、group byで指定した列以外を、列では得られません。

MySQLではこの部分に拡張仕様があり、group byで指定していない列も、「group byで結果として一意になるなら指定してよい。一意にならないなら、結果を保証しない」となっています。この拡張仕様は、インストール時の標準設定になっていて、初心者には誤りを起こしやすくなっています。

>scoreが最初に登録されたものに集約されてからグループ化されてソートしてしまう

MySQLは、そんなことは保証していませんよ？

サブクエリを使った方法になります。

SELECT no, id, score
FROM tbl
WHERE (id,score) IN(SELECT id,MAX(score) FROM tbl)
ORDER BY score DESC

SELECT x.no, x.id, x.score
FROM tbl AS x,(SELECT id,MAX(score) AS MaxScore FROM tbl) AS y
WHERE x.id=y.id AND x.score=y.MaxScore
ORDER BY x.score DESC

お礼

恥ずかしながら誤解していたところもありましたので、
こちらで質問して良かったです。
無事解決しました、ありがとうございました。