集計でテストの各教科の最高得点とその人の名前を取得するには？

質問

お世話になります。
以下のようなデータが収められた、テーブル `test1` から
各教科(subject) の最高得点(score)とその名前(name)を取得したいのですが、

name | subject | score
-----+---------+-------
鈴木 | 国語 　 | 80
山本 | 国語 　 | 50
佐藤 | 国語 　 | 90
鈴木 | 英語 　 | 80
佐藤 | 英語 　 | 60
山本 | 英語 　 | 60
鈴木 | 数学 　 | 50
佐藤 | 数学 　 | 70
山本 | 数学 　 | 90

↓以下の結果取得したい

name | subject | score
-----+---------+-------
佐藤 | 国語 　 | 90
鈴木 | 英語 　 | 80
山本 | 数学 　 | 90

SELECT `name`,`subject`,MAX(score) AS score FROM `test1` GROUP BY `subject`;

のクエリだと以下の結果で期待する結果が取得できません。

name | subject | score
-----+---------+-------
鈴木 | 国語 　 | 90
鈴木 | 数学 　 | 90
鈴木 | 英語 　 | 80

どのようにすればいいのでしょうか？
教えていただければ助かります。よろしくお願いします。

回答No.1

ESE_SE · ベストアンサー

まず考え方としては
・各教科ごとの最高点を取得する
・教科と最高点を条件に名前を取得する
とすれば良いのではないかと。
当方SQL Server使いなので記法が少々異なるかも知れませんが・・・

SELECT T.name, T.subject, T.score
FROM test1 as T
INNER JOIN
(SELECT subject, MAX(score) FROM test1 GROUP BY subject)
as T1 ON T.subject=T1.subject AND T.score=T1.score

テーブルT1として教科ごとの最高点を取得、その後テーブルTから
合致する名前を取得します。

お礼

ありがとうございます。
期待通りの結果を得ることができました。

ローカルのMySQL5.0の環境では、エラーとなるので
MAX(score) → MAX(score) AS score
とすることで動作しました。

SELECT T.name, T.subject, T.score
FROM test1 as T
INNER JOIN
(SELECT subject, MAX(score) AS score FROM test1 GROUP BY subject)
as T1 ON T.subject=T1.subject AND T.score=T1.score

回答No.3

nora1962

別解です。

select a.*
from test1 a inner join
( select subject,max(score) score
from test1
group by subject ) b
on a.subject=b.subject and a.score=b.score;

select a.*
from test1 a
where not exists
( select 1 from test1 b
where a.subject = b.subject
and a.score < b.score );

お礼

ありがとうございます
期待通りの結果を取得できました。

別解の方法は、なにか他の結果を取得するときにも使えそうですね。
勉強になりました。

回答No.2

chukenkenkou

RDBMSは、ここのカテゴリ通りMySQLですか？
バージョンは？

MySQL 4.0以前・・・一部のジョイン、union、一時表などは実装済
MySQL 4.1・・・ジョインの拡張、サブクエリ、unicodeなどの実装。一部の仕様変更。
MySQL 5.0・・・ビュー、ストアドプロシジャ、トリガなどの実装。一部の仕様変更。

MySQLであれば、すぐに思いつくだけでも、上記のような大きな機能追加や仕様変更があります。

>のクエリだと以下の結果で期待する結果が取得できません。

テストデータが良かったということですね。

MySQL 4.1以降で実行可能なSQL例を示します。

select
*
from test1 as x
where score=(select
max(score)
from test1
where subject=x.subject)

お礼

ありがとうございます。
期待通りの結果が得られました。

質問する文章をまとめるので、バージョンを書くのを忘れてました。
テスト環境は、MySQL5.0でした。