[SQL] 特定のカラムごとにまとめて最大値を持つ行を抜き出したい

Question

前提・実現したいこと

特定のカラムごとにまとめて最大値を持つ行を抜き出したい
こういうときの良いSQLの書き方を教えてほしいです。

該当のソースコード

以下のようなデータで、クラスごとに最大点をとった人の
クラス、点数、名前を抜き出したいです。
同率一位がいた場合は全員抜き出したいです。

対象のデータ
studentテーブル

class	point	name
A	30	佐藤
A	60	安田
A	60	鈴木
B	60	田中
B	80	高橋
C	30	山田
C	50	伊藤

取り出したい形

class	point	name
A	60	安田
A	60	鈴木
B	80	高橋
C	50	伊藤

以下のような副問合せで取り出せそうだと思いましたが、これだとあるクラスの最高点と同じ他の人も抜き出してしまっていました。
（この例だとAの最高点（60）と同じBの田中も抜き出してしまっていました）

SELECT
	s1.class,
	s1.point,
	s2.name
FROM (
	SELECT
		class,
		MAX(point) AS point
	FROM
		student
	GROUP BY
		class) s1
	JOIN student s2 ON s1.point = s2.point

質問したいこと

上記の取り出し方ができるSQLを知りたいです。
また取り出したいデータに対してSQLが複雑な気がするのですが、もっと簡潔な書き方はありますでしょうか。
window関数とかを使えばもっと簡潔にかけそうな気がするのですがよくわかっておりません。
例えば以下のような考え方ではできないのでしょうか。

SELECT
	class,
	name,
	MAX(point) OVER (PARTITION BY class) AS max
FROM
	student
WHERE
	point = max --動かないですが最大値と一致する行を条件にしたいつもり

Comments

[maisumakun]

同率1位がいた場合はどうするのでしょうか。

[aruto]

そこらへんを全然考慮しておりませんでした。
しかも、上のSQLだと他のクラスの最高点と同じ人も取り出してしまっていました。

同率一位がいた場合は両方とも抜き出したいです。
質問文を編集します。

Answer 1

質問の問いの部分に関しては、Maxのpointを条件にするなら、駆動側でMaxを取る必要はありません。
whereの条件とする場合は、__相関副問合せ__の形式になります。

SELECT *
FROM   student t1
WHERE  point = (
         select max(point) from student where class=t1.class
       )

ただ、DBMSによっては現状の問い合わせの方式が高速だったりします。

SELECT s2.*
FROM (
      SELECT class, MAX(point) AS point
      FROM student
      GROUP BY class
    ) s1
    inner JOIN student s2 
    ON s1.class=s2.class and s1.point = s2.point

Comments

[aruto]

ありがとうございます。
相関副問合せの形式の方はまさにこういう風だったら理解しやすいと思う形です。
まだ副問合せはパット見で理解しづらいので、まずは希望するデータを取り出せるよう練習して慣れてきたら速さにも目を向けるようにしたいと思います。