グループごとの最高得点者一覧を取得するには

質問

MySQLで、グループごとの最高得点者一覧を取得する方法を教えてください。

例えば、「生徒番号」「学年」「組」「成績」というカラムを持ったテーブルがあるとき、各クラス (「学年」と「組」のペア) から一人ずつ、最高得点を取得した生徒の「生徒番号」と「成績」を取得しようとしています。このとき「生徒番号」は一意ですが、「成績」は一意とは限りません。

また、同じクラスで最高得点者が二人以上いる場合、すべて取得か条件を指定して一人に絞るかを選べたらなおありがたいです。

【だめだった例】
SELECT 生徒番号, 成績
GROUP BY 学年, 組
ORDER BY 成績 DESC
⇒ 「GROUP BY 成績」なので「生徒番号」を SELECT できない

SELECT 生徒番号, 成績
GROUP BY 学年, 組
HAVING MAX(成績)
⇒ 同じく

SELECT 生徒番号, 成績
WHERE 成績 IN (
SELECT MAX(成績)
FROM テーブル
GROUP BY 学年, 組
)
⇒ 「成績」は一意ではないので、余分な行 (他のクラスで成績が同じ者) が取得される

回答No.1

yambejp · ベストアンサー

同率がいた場合列記する

SELECT 生徒番号,MAIN.学年, MAIN.組,MAIN.成績
FROM 成績テーブル AS MAIN
INNER JOIN (
SELECT 学年, 組,MAX(成績) AS 成績 FROM 成績テーブル
GROUP BY 学年, 組
) AS SUB USING(学年,組,成績)

一つだけ抽出する（たとえば生徒番号が若い方）

SELECT MIN(生徒番号) AS 生徒番号,MAIN.学年, MAIN.組,MAIN.成績
FROM 成績テーブル AS MAIN
INNER JOIN (
SELECT 学年, 組,MAX(成績) AS 成績 FROM 成績テーブル
GROUP BY 学年, 組
) AS SUB USING(学年,組,成績)
GROUP BY 学年,組

お礼

大変参考になりました！ありがとうございました。