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ベストアンサー
- 回答日時:
まず、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- …
例を示すと、
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
- 回答日時:
なぜ、xとyに意図しない値が入るかというと、group byされた中で、一番最初の値を拾ってしまうからです。
なので、おっしゃる通り、期待する結果がクエリ1回で得られないということになります。
ここからはお節介です。
ソートして、一時テーブルあるいは一時格納専用に作ったテーブルへインサート。
その結果を提示されたSQLで表現することは可能です。(最後に、一時格納専用テーブルのtruncate)
可能ですが、「たまたま入った」nameを表示することになります。
提示されたSQL で抜いた結果セットから、商品名をidで検索した方が無難だと思います。
大変分かりやすい解説ありがとうございます。
やはり1回では無理なんですね。スッキリしました。
> 提示されたSQL で抜いた結果セットから、商品名をidで検索した方が無難だと思います。
この方法で勉強したいともいます。
アドバイスありがとうございました。
No.2
- 回答日時:
まずはじめにサポートのきれたバージョンを使うということは自己責任です
大丈夫ですか?
さて、この手の処理は「期待する結果がクエリ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回では出来ないと聞いてスッキリしました。
また、例示もいただきましてありがとうございます。
あらためて勉強いたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- PostgreSQL 画像とカテゴリーを出力したいのですが、取得の条件を付ける方法がわかりません。 2 2022/05/01 18:03
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Access(アクセス) アクセス 有効なフィールド名、または式として認識できませんのエラー 3 2022/08/19 11:53
- MySQL SQLです。下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 「昨年の各月の総降 1 2023/07/01 00:32
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- PHP php my adminより取り出したデータ表示 2 2022/06/15 11:56
- Oracle sqlで質問です。 aテーブルとbテーブルがあり、下記のsqlで取得したidとnameに一致しないレ 1 2022/04/20 20:34
- Access(アクセス) Dlookupにエラーがでてしまう 1 2022/10/31 14:35
- Access(アクセス) アクセス テーブルの空白を変数に置換するボタンが作りたい 4 2022/07/08 11:19
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
sqlで、600行あるテーブルを100...
-
URL と行番号の指定
-
複数JOINしているとCOUNTが正し...
-
VIEWの元のテーブルのindexって...
-
エクセルの関数について教えて...
-
複数テーブルのGROUP BY の使い...
-
selectした大量データをinsert...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
inner joinをすると数がおかし...
-
insertを高速化させたい
-
MySQL5.1.14で空白を入れるとエ...
-
#1136 - Column count doesn't ...
-
SELECT句の指定フィールドに固...
-
フィールド名を変数で指定するには
-
Unionした最後にGROUP BYを追加...
-
MySQLのint型で001と表示する方...
-
テーブル名を省略して「h.id」...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
VIEWの元のテーブルのindexって...
-
SQLサーバから、項目の属性(型...
-
select文のwhere句に配列を入れ...
-
副問合せの書き方について
-
マイクラPC版のコマンドで効率...
-
Unionした最後にGROUP BYを追加...
-
selectした大量データをinsert...
-
SQLにて特定の文字を除いた検索...
-
[SQLServer] テーブル名からカ...
-
1テーブル&複数レコードの更新...
-
inner joinをすると数がおかし...
-
クエリ表示と、ADOで抽出したレ...
-
ある条件の最大値+1を初番する...
-
sqlで、600行あるテーブルを100...
-
複数テーブルのGROUP BY の使い...
-
insertを高速化させたい
-
PL/SQLの変数について
-
キー毎の、ある列のmaxのレコー...
おすすめ情報