SQLで複雑な条件で絞り込み

SQLで以下の問題を考えてみたのでメモ。

お題:人ごとに最高点のレコードのみ抽出する。最高点が複数あるときは受験日の最新のレコード1件のみとする。

-- create table
create table record (
 user_id varchar(8) not null,
 record_date date not null,
 score integer not null,
 note text,
 primary key (user_id, record_date));

-- 人ごとの最高点のみだが、同点の場合複数表示(max使用)
select * from record a
where score = (select max(score) from record b
where b.user_id = a.user_id);

ALL句でも同じことができる。でもそれ以上の条件をつけられない。

-- 人ごとの最高点のみだが、同点の場合複数表示(all句使用)
select * from record a
where score >= all(select score from record b
where b.user_id = a.user_id);

まず、人ごとの最高点で最新の日付項目で絞り込み。

select user_id,score,max(record_date) from record a
where (user_id,score) in (
select user_id,max(score) from record b group by user_id) group by user_id,score;

それをキーとして使用して全フィールドを表示する。

select * from record c
where (user_id,record_date) in
(select user_id,max(record_date) from record a
where (user_id,score) in (
select user_id,max(score) from record b group by user_id) group by user_id,score);

できた!