3-36 分析関数でのkeepの使用例

SQLパズル

テーブル
Code  Seq  SubSeq
----  ---  ------
AAAA  100       1
AAAA  150       1
AAAA  200       2
AAAA  200       3
AAAA  250       1
AAAA  250       2
BBBB  300       1
BBBB  300       2
BBBB  300       3
BBBB  300       4
BBBB  300       5
BBBB  400       1
CCCC  600       1
CCCC  650       1
CCCC  750       1
CCCC  750       2
CCCC  750       3
CCCC  750       4
CCCC  750       5

Codeごとの、Seqが最大の行の中での、SubSeqの最大値を求める。

出力結果
Code  Seq  SubSeq  maxSubSeq
----  ---  ------  ---------
AAAA  100       1          2
AAAA  150       1          2
AAAA  200       2          2
AAAA  200       3          2
AAAA  250       1          2
AAAA  250       2          2
BBBB  300       1          1
BBBB  300       2          1
BBBB  300       3          1
BBBB  300       4          1
BBBB  300       5          1
BBBB  400       1          1
CCCC  600       1          5
CCCC  650       1          5
CCCC  750       1          5
CCCC  750       2          5
CCCC  750       3          5
CCCC  750       4          5
CCCC  750       5          5

SQL

select Code,Seq,SubSeq,
max(SubSeq) Keep (Dense_Rank Last order by Seq) over(partition by Code) as maxSubSeq
  from (select 'AAAA' as Code,100 as Seq,1 as SubSeq from dual
  union select 'AAAA',150,1 from dual
  union select 'AAAA',200,2 from dual
  union select 'AAAA',200,3 from dual
  union select 'AAAA',250,1 from dual
  union select 'AAAA',250,2 from dual
  union select 'BBBB',300,1 from dual
  union select 'BBBB',300,2 from dual
  union select 'BBBB',300,3 from dual
  union select 'BBBB',300,4 from dual
  union select 'BBBB',300,5 from dual
  union select 'BBBB',400,1 from dual
  union select 'CCCC',600,1 from dual
  union select 'CCCC',650,1 from dual
  union select 'CCCC',750,1 from dual
  union select 'CCCC',750,2 from dual
  union select 'CCCC',750,3 from dual
  union select 'CCCC',750,4 from dual
  union select 'CCCC',750,5 from dual);

解説

分析関数でもkeepを使うことができます。
使用頻度は、集合関数のkeepのほうが高そうですが

マニュアル(分析関数でのkeepの使用例)(英語)

---