5-1 グループごとに最大値の行を取得

SQLパズル

Table540テーブル
COL1  COL2  COL3  COL4
----  ----  ----  ----
   1     1     1     3
   2     1     2     6
   3     1     3     9
   3     2     4     3
   3     2     5     6
   3     2     6     9
   4     3     7     3

Table540テーブルの、Col2のグループごとの最大のCol1を持つ行を出力する

出力結果
COL1  COL2  COL3  COL4
----  ----  ----  ----
   3     1     3     9
   3     2     4     3
   3     2     5     6
   3     2     6     9
   4     3     7     3

データ作成スクリプト

create Table Table540(
Col1 int,
Col2 int,
Col3 int,
Col4 int);

insert into Table540 values
(1,1,1,3),(2,1,2,6),(3,1,3,9),
(3,2,4,3),(3,2,5,6),(3,2,6,9),
(4,3,7,3);

SQL

#■■■in述語を使う方法■■■
select Col1,Col2,Col3,Col4 from Table540 a
where (Col1,Col2) in (select max(b.Col1),b.Col2 from Table540 b group by b.Col2)
order by Col2,Col1,Col3;

#■■■existsを使う方法■■■
select Col1,Col2,Col3,Col4 from Table540 a
where not exists(select 1 from Table540 b
                  where b.Col2 = a.Col2
                    and b.Col1 > a.Col1)
order by Col2,Col1,Col3;

#■■■サブクエリを使わない方法■■■
select a.COL1,a.COL2,a.COL3,a.COL4
  from Table540 a
Left Join Table540 b
on (a.COL2 = b.COL2
and b.COL1 > a.COL1)
where b.COL1 is null;

解説

Col2のグループごとのCol1の最大値と等しいことを条件としてます