MySQLで重複する値を含む複数の最大値を取得する方法

MySQLで最大値が重複している複数行を取得するには

テーブル内の最大値が複数のレコードに重複して存在する場合、単純な MAX() 関数だけでは該当するすべての行を取得できません。このような場合は、JOIN（結合）の概念を使うことで、重複する最大値を持つすべてのレコードを抽出できます。

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable1389
-> (
-> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> StudentMarks int
-> );
Query OK, 0 rows affected (2.73 sec)

サンプルデータの挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。ここでは、意図的に重複する値も含めています。

mysql> insert into DemoTable1389(StudentMarks) values(40);
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable1389(StudentMarks) values(40);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1389(StudentMarks) values(68);
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable1389(StudentMarks) values(78);
Query OK, 1 row affected (0.43 sec)
mysql> insert into DemoTable1389(StudentMarks) values(97);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1389(StudentMarks) values(97);
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable1389(StudentMarks) values(97);
Query OK, 1 row affected (0.49 sec)

SELECT文ですべてのレコードを表示して確認してみましょう。

mysql> select * from DemoTable1389;

このクエリを実行すると、以下のような出力が得られます。

+-----------+--------------+
| StudentId | StudentMarks |
+-----------+--------------+
|         1 |           40 |
|         2 |           40 |
|         3 |           68 |
|         4 |           78 |
|         5 |           97 |
|         6 |           97 |
|         7 |           97 |
+-----------+--------------+
7 rows in set (0.00 sec)

この例では、最大値「97」が3つのレコードに重複して存在しています。次に、これらの重複する最大値をすべて選択する方法を見ていきましょう。

重複する最大値を取得するクエリ

以下のように、サブクエリで最大値を求め、それを元のテーブルとJOINすることで、最大値を持つすべての行を取得できます。

mysql> select tbl.StudentId,tbl.StudentMarks from DemoTable1389 tbl
-> join ( select max(StudentMarks) as MaxMarks from DemoTable1389) tbl1
-> on tbl1.MaxMarks=tbl.StudentMarks;

実行結果

このクエリを実行すると、重複分を含めた最大値を持つすべてのレコードが表示されます。

+-----------+--------------+
| StudentId | StudentMarks |
+-----------+--------------+
|         5 |           97 |
|         6 |           97 |
|         7 |           97 |
+-----------+--------------+
3 rows in set (0.00 sec)

仕組みの解説

このクエリが機能する理由は次のとおりです。

1. サブクエリ ( select max(StudentMarks) as MaxMarks from DemoTable1389) が、テーブル全体の最大値「97」を1つの値として返します。
2. メインクエリは、この最大値と元のテーブルの StudentMarks 列を結合条件としてJOINします。
3. その結果、StudentMarksが「97」と一致するすべての行（重複行を含む）が結果セットとして取得されます。

この手法は、ランキングや成績など、同じ最高値を持つ複数のレコードをすべて抽出したい場合に非常に便利です。ぜひ活用してください。