5-36 in述語の引数に分析関数

SQLパズル

テーブルA(プライマリキーは、Col1,Col2,Col3)
Col1  Col2  Col3
----  ----  ----
   1    5      5
   1    5      4
   1    1      9
   2    9      1
   2    8      9
   2    1      1
   3    9      1
   3    5    100
   3    1   1000

テーブルAから、Col1ごとの、Col2の最大値(複数あったら、Col3が最大のデータ)
を出力する

出力結果
Col1  Col2  Col3
----  ----  ----
   1    5      5
   2    9      1
   3    9      1

データ作成スクリプト

create Table テーブルA(
Col1 number(1),
Col2 number(1),
Col3 number(4),
primary key(Col1,Col2,Col3));

insert into テーブルA values(1,5,5);
insert into テーブルA values(1,5,4);
insert into テーブルA values(1,1,9);
insert into テーブルA values(2,9,1);
insert into テーブルA values(2,8,9);
insert into テーブルA values(2,1,1);
insert into テーブルA values(3,9,1);
insert into テーブルA values(3,5,100);
insert into テーブルA values(3,1,1000);
commit;

SQL

--■■■in述語とRow_Number関数を使う方法■■■
select Col1,Col2,Col3 from テーブルA
where (RowID,1) in
(select b.RowID,
 Row_Number() over(partition by b.Col1 order by b.Col2 desc,b.Col3 desc)
 from テーブルA b)
order by Col1;

--■■■in述語とFirst_Value関数を使う方法■■■
select Col1,Col2,Col3 from テーブルA
where RowID in
(select First_Value(b.RowID)
        over(partition by b.Col1 order by b.Col2 desc,b.Col3 desc)
 from テーブルA b)
order by Col1;

--■■■in述語を使わない方法1■■■
select Col1,Col2,Col3 from
(select Col1,Col2,Col3,
 Row_Number() over(partition by Col1 order by Col2 desc,Col3 desc) as Rank
 from テーブルA)
where Rank=1
order by Col1;

--■■■in述語を使わない方法2■■■
select Col1,max(Col2) as Col2,
max(Col3) keep(Dense_Rank Last order by Col2) as Col3
from テーブルA
group by Col1
order by Col1;

解説

in述語の引数に、分析関数を使ったselect文を使うことができます