MySQLで複数の列にGROUP BYとMAXを組み合わせて使う方法

MySQLでは、カテゴリごとの最大値を求めるときに GROUP BY と MAX() を組み合わせるのが定番ですが、複数の列に対して段階的に最大値を取得したいケースでは、少し工夫が必要です。本記事では、サブクエリと内部結合（INNER JOIN）を活用して、2つの列にまたがる最大値を取得する方法を具体的な例とともに解説します。

1. サンプルテーブルの作成

まず、動作確認用のテーブルを作成します。以下がテーブル作成のクエリです。

mysql> create table GroupByMaxDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> CategoryId int,
-> Value1 int,
-> Value2 int
-> );
Query OK, 0 rows affected (0.68 sec)

このテーブルは、CategoryId（カテゴリID）、Value1、Value2 の3つのデータ列を持つシンプルな構造です。

2. テストデータの挿入

次に、INSERT文を使ってサンプルレコードを挿入します。

mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,100,50);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,100,70);
Query OK, 1 row affected (0.21 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(10,50,100);
Query OK, 1 row affected (0.22 sec)
mysql> insert into GroupByMaxDemo(CategoryId, Value1,Value2) values(20,180,150);
Query OK, 1 row affected (0.19 sec)

SELECT文ですべてのレコードを表示して、データが正しく登録されたか確認しましょう。

mysql> select *from GroupByMaxDemo;

出力結果

+----+------------+--------+--------+
| Id | CategoryId | Value1 | Value2 |
+----+------------+--------+--------+
|  1 |         10 |    100 |     50 |
|  2 |         10 |    100 |     70 |
|  3 |         10 |     50 |    100 |
|  4 |         20 |    180 |    150 |
+----+------------+--------+--------+
4 rows in set (0.00 sec)

3. 複数の列でGROUP BYとMAXを使用するクエリ

ここからが本題です。目標は「各カテゴリにおける Value1 の最大値を求め、さらにその中で Value2 の最大値を取得する」という2段階の集計です。単純な GROUP BY だけでは実現できないため、サブクエリと自己結合を組み合わせます。

以下がそのクエリです。

mysql> select tbl2.CategoryId, tbl2.Value1, max(tbl2.Value2)
-> from
-> (
-> select CategoryId, max(Value1) as `Value1`
-> from GroupByMaxDemo
-> group by CategoryId
-> ) tbl1
-> inner join GroupByMaxDemo tbl2 on tbl2.CategoryId = tbl1.CategoryId and tbl2.Value1 = tbl1.Value1
-> group by tbl2.CategoryId, tbl2.Value1;

クエリの仕組み

このクエリは、次の流れで処理されます。

ステップ1： 内側のサブクエリ（tbl1）で、CategoryId ごとに Value1 の最大値を算出します。
ステップ2： 元のテーブル（tbl2）と CategoryId および Value1 をキーに内部結合し、最大の Value1 を持つ行だけを抽出します。
ステップ3： 最後に外側の GROUP BY で、その条件に合う行の中から Value2 の最大値を取得します。

出力結果

+------------+--------+------------------+
| CategoryId | Value1 | max(tbl2.Value2) |
+------------+--------+------------------+
|         10 |    100 |               70 |
|         20 |    180 |              150 |
+------------+--------+------------------+
2 rows in set (0.00 sec)

結果の解説

出力を見ると、カテゴリ10では Value1 の最大値「100」に該当する行が2つあり（Id=1 と Id=2）、その中で Value2 の最大値である「70」が返されています。同様に、カテゴリ20では Value1=180 の行の Value2 最大値「150」が取得できています。

このように、サブクエリで第一段階の最大値を絞り込み、結合後に第二段階の集計を行うことで、複数の列にまたがる GROUP BY と MAX の処理を柔軟に実現できます。MySQL 8.0以降であれば、ウィンドウ関数（ROW_NUMBER() など）を使った別のアプローチも可能なので、状況に応じて使い分けるとよいでしょう。