MySQLで特定の列のグループごとの最大値を保持する行を取得する方法

MySQLでは、「カテゴリごとに特定の列が最大値となっている行」を取り出したい場面がよくあります。たとえば「商品ごとに最も高い価格のレコードだけを取得したい」といったケースです。本記事では、相関サブクエリ（相関副問い合わせ）を使ってこの問題を解決する方法を解説します。

基本構文

グループごとの最大値を保持する行を取得するには、以下のようなSQL構文を使用します。

SELECT colName1, colName2, colName3
FROM tableName s1
WHERE colName3 = (
    SELECT MAX(s2.colName3)
    FROM tableName s2
    WHERE s1.colName1 = s2.colName1
)
ORDER BY colName1;

ポイントは、サブクエリ内で外側のクエリの列（s1.colName1）と比較している点です。これにより、同じグループ（ここではcolName1が同じ値）の中でのみ最大値が計算されます。

サンプルテーブル：PRODUCT

説明のため、次のような商品テーブルを用意します。

+---------+-----------+--------+
| Article | Warehouse | Price  |
+---------+-----------+--------+
| 1       | North     | 255.50 |
| 1       | North     | 256.05 |
| 2       | South     | 90.50  |
| 3       | East      | 120.50 |
| 3       | East      | 123.10 |
| 3       | East      | 122.10 |
+---------+-----------+--------+

このテーブルには、同じ商品番号（Article）に対して複数の価格レコードが存在します。

クエリ例

商品ごとに最も高い価格のレコードを取得するクエリは以下の通りです。

SELECT Article, Warehouse, Price
FROM Product p1
WHERE Price = (
    SELECT MAX(p2.Price)
    FROM Product p2
    WHERE p1.Article = p2.Article
)
ORDER BY Article;

実行結果

+---------+-----------+--------+
| Article | Warehouse | Price  |
+---------+-----------+--------+
| 0001    | North     | 256.05 |
| 0002    | South     | 90.50  |
| 0003    | East      | 123.10 |
+---------+-----------+--------+

各商品番号について、最も高い価格の行だけが抽出されていることがわかります。

仕組みの解説：相関サブクエリとは

上記のクエリは相関サブクエリ（correlated subquery）を使用しています。通常のサブクエリは一度だけ実行されますが、相関サブクエリは外側のクエリの各行に対して繰り返し実行される点が特徴です。

処理の流れは以下の通りです。

• 外側のクエリから1行取り出す（例：Article = 1 の行）。
• サブクエリが、その行と同じ Article を持つレコード群の中で MAX(Price) を計算する。
• 外側の行の Price がその最大値と一致すれば、その行が結果に含まれる。
• これを全行に対して繰り返す。

注意点と代替手法

相関サブクエリは直感的で分かりやすい一方、行数が多いテーブルではパフォーマンスが低下しやすいという欠点があります。大規模なデータを扱う場合は、以下の方法も検討するとよいでしょう。

• ウィンドウ関数（MySQL 8.0以降）：ROW_NUMBER() OVER (PARTITION BY Article ORDER BY Price DESC) を使って各グループの上位1件を効率的に取得できます。
• GROUP BY + JOIN：グループごとの最大値を求めるサブクエリを先に作成し、元テーブルと結合する方法です。

状況に応じて最適な手法を選択することで、正確かつ高速にグループごとの最大値行を取得できます。