GROUP BY/ORDER BYの併用で期待するORDERが得られま

質問

GROUP BY/ORDER BYの併用で期待するORDERが得られません。
データベース初心者です。よろしくお願いいたします。

table
id group_id name
1 1 冷蔵庫1
2 2 カメラ1
3 2 カメラ2
4 2 カメラ3
5 3 テレビ1

のようなテーブルを、group_idでグループ化し、idの大きい順に並べた以下のようなデータ

id group_id name
5 3 テレビ1
4 2 カメラ3
1 1 冷蔵庫1

を取得しようとして悩んでおります。

SELECT MAX(id) AS max_id, id, group_id, name
FROM table
GROUP BY group_id
ORDER BY max_id DESC

というクエリですと

max_id id group_id name
5 5 3 テレビ1
4 x 2 y
1 1 1 冷蔵庫1

という結果が得られますが、xとyところが、期待するx=4、y=カメラ3とならないケースがあります。
いくつかのテーブルで試しましたが、期待する結果になるものとならないものがありました。

例えばこれを自己結合することも考えたのですが、WHERE句にASで命名した「max_id」が含まれると「max_idが不明」というエラーが出ます。

MySQLのバージョンは4.0.xでして、サブクエリーが使えません。
このような状況で、何か方法がございましたらご教授のほどお願い申し上げます。
「期待する結果がクエリ1回で得られない」ということを知れるだけでも構いません。
どうぞよろしくお願いいたします。

回答No.1

chukenkenkou · ベストアンサー

まず、GROUP BYの使い方に誤りがあります。

標準SQLおよび主要なRDBMSの実装では、GROUP BY使用時、SELECT句での選択リストに書けるのは、

(1)GROUP BYで指定した列
(2)MAXやCOUNTなどの集約（集合）関数
(3)定数

といったものだけです。
なぜかというと、GROUP BYにより1個以上の行を集約する訳ですから、それ以外の列は、どの値を選べばいいかが決められません。

しかし、MySQLではこの部分に拡張仕様を持っていて、
「GROUP BYでグループ化した結果、結果が一意になるならSELECT句でその列名を書いてよい。一意にならないなら、結果は保証しないし、MySQLの標準の設定ではエラーにもしない」
という、誤りを誘発しやすい仕様になっています。

http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

例を示すと、

SELECT c1,MAX(c2),c3 FROM t1 GROUP BY c1

といった書き方は、c3の値が決められないため、標準SQLでは誤りですし、主要なRDBMSでもエラーになります。
しかし、MySQLでは、「c1でグループ化した結果、c3の値が一意になるならそういう書き方でいい。一意にならないなら、結果は保証しない。エラーにもしない」です。

これは、例えばidとnameが一対であるような場合、分かっていて利用するには便利なのですが、知らないで使っている人、特に初心者が多いと感じでいます。

本来は、

SELECT id,name,SUM(score) FROM t1 GROUP BY id,name

と書かなければだめなものを

SELECT id,name,SUM(score) FROM t1 GROUP BY id

とできるというものです。
逆にいうと、上の書き方と下の書き方で結果が違うなら、使ってはいけない拡張仕様を使っていることになります。

お礼

大変分かりやすい解説ありがとうございます。
霧が晴れました。

MySQLしか扱ったことがなかったのですが、視野を広げて勉強してみます。
ありがとうございました。

回答No.3

moousi

なぜ、xとyに意図しない値が入るかというと、group byされた中で、一番最初の値を拾ってしまうからです。

なので、おっしゃる通り、期待する結果がクエリ1回で得られないということになります。

ここからはお節介です。

ソートして、一時テーブルあるいは一時格納専用に作ったテーブルへインサート。
その結果を提示されたSQLで表現することは可能です。（最後に、一時格納専用テーブルのtruncate）
可能ですが、「たまたま入った」nameを表示することになります。

提示されたSQL で抜いた結果セットから、商品名をidで検索した方が無難だと思います。

お礼

大変分かりやすい解説ありがとうございます。
やはり1回では無理なんですね。スッキリしました。

＞　提示されたSQL で抜いた結果セットから、商品名をidで検索した方が無難だと思います。
この方法で勉強したいともいます。
アドバイスありがとうございました。

回答No.2

yambejp

まずはじめにサポートのきれたバージョンを使うということは自己責任です
大丈夫ですか？

さて、この手の処理は「期待する結果がクエリ1回で得られない」です。
テンポラリを作ってやるのが普通です。

create temporary table temp_table
select max(id) as id,group_id from hoge
group by hoge.group_id;
select temp_table.id,temp_table.group_id,hoge.name
from temp_table
inner join hoge using(id)
order by hoge.id desc;

お礼

ご回答ありがとうございます。
1回では出来ないと聞いてスッキリしました。
また、例示もいただきましてありがとうございます。
あらためて勉強いたします。