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

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 number(1),
Col2 number(1),
Col3 number(1),
Col4 number(1));

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

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 Col1,Col2,Col3,Col4
from (select Col1,Col2,Col3,Col4,
      max(Col1) over(partition by Col2) as MaxCol1
      from Table540)
where Col1=MaxCol1
order by Col2,Col1,Col3;

解説

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