質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.33%

  • MySQL

    6178questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQLで2つのカラムの最大を取りたい

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 2,799

msx2

score 149

code,version,variationという3つのカラムを持つテーブルがあります。
同じcode内で最大のversion中のさらに最大のvariationをSQLで取得する方法はないでしょうか?

code,version,variation
AAA,1,1
AAA,2,1
AAA,2,2
AAA,2,3
BBB,1,1
BBB,1,2
BBB,2,1

上記のデータだと欲しいのは
AAA,2,3
BBB,2,1
です。

単純にGroup byでMAXを取ると
AAA,2,3
BBB,2,2
となってしまいます。

この様なデータはSQLで取得するのは難しいでしょうか?
MySQLを使っています。

多少複雑になってもいいのでSQL一発で抽出する方法があれば教えてください。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+4

SELECT t1.*
FROM test AS t1
LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
WHERE t2.code IS NULL
  AND t3.code IS NULL;


実行結果 :

mysql> CREATE TABLE test (
    ->   code varchar(4),
    ->   version int,
    ->   variation int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES
    -> ('AAA', 1, 1),
    -> ('AAA', 2, 1),
    -> ('AAA', 2, 2),
    -> ('AAA', 2, 3),
    -> ('BBB', 1, 1),
    -> ('BBB', 1, 2),
    -> ('BBB', 2, 1);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT t1.*
    -> FROM test AS t1
    -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
    -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
    -> WHERE t2.code IS NULL
    ->   AND t3.code IS NULL;
+------+---------+-----------+
| code | version | variation |
+------+---------+-----------+
| AAA  |       2 |         3 |
| BBB  |       2 |         1 |
+------+---------+-----------+
2 rows in set (0.00 sec)

 追記

一つずつ実行してようやく理解できました。
自分より大きいversionがないデータがNULLになって最大を求めているんですね。
そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。

Good Job!
まさに、仰る通りです。

以下のように SELECTリストを*に変更し、さらに WHERE句を削除したクエリを実行してみると、
よりイメージが掴みやすくなるかと思います。

mysql> SELECT *
    -> FROM test AS t1
    -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
    -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation;
+------+---------+-----------+------+---------+-----------+------+---------+-----------+
| code | version | variation | code | version | variation | code | version | variation |
+------+---------+-----------+------+---------+-----------+------+---------+-----------+
| BBB  |       1 |         1 | BBB  |       2 |         1 | BBB  |       1 |         2 |
| AAA  |       1 |         1 | AAA  |       2 |         1 | NULL |    NULL |      NULL |
| AAA  |       1 |         1 | AAA  |       2 |         2 | NULL |    NULL |      NULL |
| AAA  |       1 |         1 | AAA  |       2 |         3 | NULL |    NULL |      NULL |
| BBB  |       1 |         2 | BBB  |       2 |         1 | NULL |    NULL |      NULL |
| AAA  |       2 |         1 | NULL |    NULL |      NULL | AAA  |       2 |         2 |
| AAA  |       2 |         1 | NULL |    NULL |      NULL | AAA  |       2 |         3 |
| AAA  |       2 |         2 | NULL |    NULL |      NULL | AAA  |       2 |         3 |
| AAA  |       2 |         3 | NULL |    NULL |      NULL | NULL |    NULL |      NULL |
| BBB  |       2 |         1 | NULL |    NULL |      NULL | NULL |    NULL |      NULL |
+------+---------+-----------+------+---------+-----------+------+---------+-----------+
10 rows in set (0.02 sec)

ちなみにこれはgroup byでも同じようなことができるのでしょうか?

実は、本回答は以下のページの"LEFT JOIN"バージョンの応用です。
https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html

もし、ご興味があれば、上のページの"相関サブクエリー"バージョンと"非相関サブクエリー"バージョンで同様の結果を得られないか、チャレンジしてみてください。
(私は力尽きたので、これ以上は深追いしません(^^; )


パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?

インデックスの有無やレコード数、値の分布具合にもよるので、何とも言えません。
ご自身の環境でそれぞれのバージョンのクエリを実行し、比較してみてください。

もっとも、手元の環境で確認した限りでは、
私の回答のクエリで(code, version, variation)という複合インデックスが効きました。

そのため、上記複合インデックスを作成すれば、
"相関サブクエリー"バージョン・"非相関サブクエリー"バージョンのクエリと比べて
「明らかに分が悪い」
ということにはならないかと、予想します。

mysql> ALTER TABLE test ADD INDEX (code, version, variation);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT t1.*
    -> FROM test AS t1
    -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
    -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
    -> WHERE t2.code IS NULL
    ->   AND t3.code IS NULL;
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                          | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | code | 21      | NULL                         |    7 |   100.00 | Using index              |
|  1 | SIMPLE      | t2    | NULL       | ref   | code          | code | 11      | test.t1.code                 |    3 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | t3    | NULL       | ref   | code          | code | 16      | test.t1.code,test.t1.version |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.05 sec)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/20 19:52

    ご回答ありがとうございます。
    凄い!!イマイチ理解できないのですが確かに期待通りのデータが得られました!
    自力では絶対にこのSQLにはたどり着かなかったと思います。

    もしよければ補足いただけないでしょうか。。?厚かましい話ですが…。

    キャンセル

  • 2017/01/20 20:16

    一つずつ実行してようやく理解できました。
    自分より大きいversionがないデータがNULLになって最大を求めているんですね。
    そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。

    それぞれの最大がNULLのデータになるので、今回は両方NULLのデータを使いましたが、s3だけNULLのデータはそのversion内での最大variationになるんですね!!

    ちなみにこれはgroup byでも同じようなことができるのでしょうか?
    パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?

    キャンセル

  • 2017/01/20 21:14

    msx2様

    レスありがとうございます。

    回答欄に返答を追記させていただきましたので、ご確認ください。

    キャンセル

  • 2017/01/20 21:27

    追記ありがとうございます。
    SQLは嫌いではないのでこれからも勉強してレベルアップを目指したいと思います。

    下手すると1日悩むところが10分で解決してしまったという、これがまさにteratailなんですね

    キャンセル

0

手元でSQLを書ける環境がないので概念のみになりますが、

  1. codeごとにGROPU BYして、MAX(version)とcodeの2つを並べてSELECT
  2. codeとversionでGROPU BYして、code, version, MAX(variation)の3つをSELECT

上の2つのサブクエリをINNER JOINすれば、目的のものは取れると思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/20 19:54

    ご回答ありがとうございました!!

    キャンセル

同じタグがついた質問を見る

  • MySQL

    6178questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。