MySQLで各グループの上位2行を取得する方法｜サブクエリを使った実装例

MySQLで「各グループごとに上位2行だけを取得したい」というケースは、売上ランキングや成績一覧など、実務でもよく遭遇する課題です。GROUP BYでは各グループの集計値しか取得できないため、こうした要件にはWHERE句と相関サブクエリを組み合わせる方法が有効です。

この記事では、サンプルテーブルを作成しながら、具体的なSQLの書き方を順を追って解説します。

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

まず、名前（Name）と合計スコア（TotalScores）を持つテーブルを作成します。

mysql> create table selectTop2FromEachGroup
    -> (
    -> Name varchar(20),
    -> TotalScores int
    -> );
Query OK, 0 rows affected (0.80 sec)

2. テストデータの挿入

次に、INSERT文でレコードを挿入します。「John」と「Carol」の2人について、それぞれ3件ずつのスコアを登録します。

mysql> insert into selectTop2FromEachGroup values('John',32);
Query OK, 1 row affected (0.38 sec)
mysql> insert into selectTop2FromEachGroup values('John',33);
Query OK, 1 row affected (0.21 sec)
mysql> insert into selectTop2FromEachGroup values('John',34);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',35);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',36);
Query OK, 1 row affected (0.14 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',37);
Query OK, 1 row affected (0.15 sec)

SELECT文で全レコードを確認してみましょう。

mysql> select *from selectTop2FromEachGroup;

実行結果は以下の通りです。

+-------+-------------+
| Name  | TotalScores |
+-------+-------------+
| John  |          32 |
| John  |          33 |
| John  |          34 |
| Carol |          35 |
| Carol |          36 |
| Carol |          37 |
+-------+-------------+
6 rows in set (0.00 sec)

3. 各グループの上位2行を取得するクエリ

ここからが本題です。WHERE句の中で相関サブクエリを使用し、「同じ名前のグループ内で、自分以上のスコアを持つ行が2行以下であるもの」だけを抽出します。

mysql> select *from selectTop2FromEachGroup tbl
    -> where
    -> (
    ->    SELECT COUNT(*)
    ->    FROM selectTop2FromEachGroup tbl1
    ->    WHERE tbl1.Name = tbl.Name AND
    ->    tbl1.TotalScores >= tbl.TotalScores
    -> ) <= 2 ;

実行結果は以下の通りです。

+-------+-------------+
| Name  | TotalScores |
+-------+-------------+
| John  |          33 |
| John  |          34 |
| Carol |          36 |
| Carol |          37 |
+-------+-------------+
4 rows in set (0.06 sec)

4. クエリの仕組みを解説

このクエリのポイントは、外側の各行に対してサブクエリが実行される「相関サブクエリ」として動作している点です。

• tbl1.Name = tbl.Name：同じグループ（ここでは同じ名前）の行だけを比較対象に限定します。
• tbl1.TotalScores >= tbl.TotalScores：自分自身を含め、自分以上のスコアを持つ行数をカウントします。
• COUNT(*) <= 2：そのカウントが2以下、つまり「上位2位以内に入っている行」のみを抽出します。

この条件により、Johnグループでは34点・33点、Carolグループでは37点・36点という、それぞれ上位2件のレコードが正しく取得できています。

補足：MySQL 8.0以降ならウィンドウ関数も使える

MySQL 8.0以降を使用している場合は、ROW_NUMBER()などのウィンドウ関数を使うと、より直感的に記述できます。

SELECT Name, TotalScores
FROM (
  SELECT Name, TotalScores,
         ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TotalScores DESC) AS rn
  FROM selectTop2FromEachGroup
) AS ranked
WHERE rn <= 2;

大量データを扱う場合や可読性を重視する場合は、こちらの方法も検討するとよいでしょう。