8-9 first_Value関数を模倣(ignore nullsなし)

SQLパズル

FirstValueTable1
Code  SortKey  Val
----  -------  ---
AAAA      100  999
AAAA      200  888
AAAA      300  777
AAAA      400  666
BBBB      500  555
BBBB      600  444
BBBB      610  333
BBBB      620  888
BBBB      630  999
CCCC      100  333

select Code,SortKey,Val,
First_Value(Val) over(partition by Code order by SortKey) as "first_Value"
from FirstValueTable1;
を模倣する。

出力結果
Code  SortKey  Val  first_Value
----  -------  ---  -----------
AAAA      100  999          999
AAAA      200  888          999
AAAA      300  777          999
AAAA      400  666          999
BBBB      500  555          555
BBBB      600  444          555
BBBB      610  333          555
BBBB      620  888          555
BBBB      630  999          555
CCCC      100  333          333

データ作成スクリプト

create table FirstValueTable1 as
select 'AAAA' as Code,100 as SortKey,999 as Val from dual
union select 'AAAA',200,888 from dual
union select 'AAAA',300,777 from dual
union select 'AAAA',400,666 from dual
union select 'BBBB',500,555 from dual
union select 'BBBB',600,444 from dual
union select 'BBBB',610,333 from dual
union select 'BBBB',620,888 from dual
union select 'BBBB',630,999 from dual
union select 'CCCC',100,333 from dual;

SQL

--■■■min関数を使う方法■■■
select Code,SortKey,Val,
max(decode(SortKey,minSortKey,Val)) over(partition by Code) as "first_Value"
from (select Code,SortKey,Val,
      min(SortKey) over(partition by Code) as minSortKey
        from FirstValueTable1);

--■■■keepを使う方法■■■
select Code,SortKey,Val,
max(Val) Keep (Dense_Rank First order by SortKey)
         over(partition by Code) as "first_Value"
from FirstValueTable1;

解説

min関数を使う方法は、
DB2やSQL Sreverでも応用できるでしょう。

---