8-55 次の入社日を求めるのアレンジ問題1

SQLパズル

ID  sortKey  Val
--  -------  ----
10        1  AAAA
10        2  FFFF
10        3  BBBB
20        5  GGGG
20        6  CCCC
20        8  HHHH
30        4  DDDD
30        5  IIII
40        4  EEEE
40        6  JJJJ

次のIDでsortKeyが最小の行のValを求める。

出力結果
ID  sortKey  Val   nextVal
--  -------  ----  -------
10        1  AAAA  GGGG
10        2  FFFF  GGGG
10        3  BBBB  GGGG
20        5  GGGG  DDDD
20        6  CCCC  DDDD
20        8  HHHH  DDDD
30        4  DDDD  EEEE
30        5  IIII  EEEE
40        4  EEEE  null
40        6  JJJJ  null

SQLクックブックのレシピ8.7を参考にさせていただきました

データ作成スクリプト

create table dupLeadT(ID,sortKey,Val) as
select 10,1,'AAAA' from dual union all
select 10,2,'FFFF' from dual union all
select 10,3,'BBBB' from dual union all
select 20,5,'GGGG' from dual union all
select 20,6,'CCCC' from dual union all
select 20,8,'HHHH' from dual union all
select 30,4,'DDDD' from dual union all
select 30,5,'IIII' from dual union all
select 40,4,'EEEE' from dual union all
select 40,6,'JJJJ' from dual;

SQL

select ID,sortKey,Val,
Lead(Val,RevRank) over(order by ID,sortKey) as nextVal
from (select ID,sortKey,Val,
      Row_Number() over(partition by ID order by sortKey desc) as RevRank
        from dupLeadT)
order by ID,sortKey;

解説

Row_Number関数の結果を、Lead関数の引数に使用してます。
8-7 次の入社日を求める

ちなみに、次の次のIDでsortKeyが最小の行のValを求めるのであれば、
下記のようなSQLとなります。

select ID,sortKey,Val,
max(firVal) over(order by rn range between 2 following
                                       and 2 following) as next2Val
from (select ID,sortKey,Val,
      dense_rank() over(order by ID) as rn,
      First_Value(Val) over(partition by ID order by sortKey) as firVal
        from dupLeadT)
order by ID,sortKey;

ID  sortKey  Val   next2Val
--  -------  ----  --------
10        1  AAAA  DDDD
10        2  FFFF  DDDD
10        3  BBBB  DDDD
20        5  GGGG  EEEE
20        6  CCCC  EEEE
20        8  HHHH  EEEE
30        4  DDDD  null
30        5  IIII  null
40        4  EEEE  null
40        6  JJJJ  null

---