5-42 Last関数

SQLパズル

Table542テーブル
KEY1  KEY2  KEY3
----  ----  ----
   1     1     1
   1     2     3
   2     1     2
   3     3     3
   3     3     4

KEY1ごとに、KEY2 desc, KEY3 descでソートした最初のレコードを出力する。

出力結果
KEY1  KEY2  KEY3
----  ----  ----
   1     2     3
   2     1     2
   3     3     4

データ作成スクリプト

create Table Table542(
KEY1 number(1),
KEY2 number(1),
KEY3 number(1));

insert into Table542 values(1,1,1);
insert into Table542 values(1,2,3);
insert into Table542 values(2,1,2);
insert into Table542 values(3,3,3);
insert into Table542 values(3,3,4);
commit;

SQL

--■■■Last関数を使う方法■■■
select KEY1,max(KEY2) as KEY2,
max(KEY3) keep(dense_rank Last order by KEY2) as KEY3
from Table542
group by KEY1
order by KEY1;

--■■■相関サブクエリを使う方法■■■
select KEY1,KEY2,KEY3
from Table542 a
where KEY2=(select max(b.KEY2) from Table542 b
             where b.KEY1=a.KEY1)
  and KEY3=(select max(c.KEY3) from Table542 c
             where c.KEY1=a.KEY1
               and c.KEY2=(select max(b.KEY2) from Table542 b
                            where b.KEY1=a.KEY1))
order by KEY1;

--■■■分析関数を使う方法■■■
select KEY1,KEY2,KEY3
from (select KEY1,KEY2,KEY3,
      Row_Number() over(partition by KEY1 order by KEY2 desc,KEY3 desc) as Rank
      from Table542)
where Rank=1
order by KEY1;

解説

Last関数を使うと、
ソート順位が最後の行の集合内での、最大値や最小値などを取得できます。