5-56 nullを最も小さいとし、最小値の行を取得

SQLパズル

CodeTable
Code      Day1  Val
----  --------  ---
aaaa  2007/3/1  100  ←出力対象
aaaa  2007/3/2  200
aaaa  2007/3/3  300
bbbb  2007/3/4  400  ←出力対象
cccc  null      500  ←出力対象
cccc  2007/3/1  600
cccc  2007/3/2  700
dddd  null      800  ←出力対象

Codeごとに、Day1が最小の行を出力する。
ただし、
Day1の大小比較においては、nullが最も小さいとする。

データ作成スクリプト

create table CodeTable as
select 'aaaa' as Code,to_date('2007/3/1','fmyyyy/mm/dd') as Day1,100 as Val from dual
union select 'aaaa',to_date('2007/3/2','fmyyyy/mm/dd'),200 from dual
union select 'aaaa',to_date('2007/3/3','fmyyyy/mm/dd'),300 from dual
union select 'bbbb',to_date('2007/3/4','fmyyyy/mm/dd'),400 from dual
union select 'cccc',                              null,500 from dual
union select 'cccc',to_date('2007/3/1','fmyyyy/mm/dd'),600 from dual
union select 'cccc',to_date('2007/3/2','fmyyyy/mm/dd'),700 from dual
union select 'dddd',                              null,800 from dual;

SQL

--■■■分析関数を使う方法1■■■
select distinct Code,
First_Value(Day1) over(partition by Code order by Day1 nulls first) as Day1,
First_Value(Val)  over(partition by Code order by Day1 nulls first) as Val
  from CodeTable;

--■■■分析関数を使う方法2■■■
select Code,Day1,Val
from (select Code,Day1,Val,
      Rank() over(partition by Code order by Day1 nulls first) as Rank
        from CodeTable)
where Rank = 1
order by Code;

--■■■分析関数を使わない方法■■■
select Code,Day1,Val
  from CodeTable a
 where Day1 is null
    or Day1 = (select min(b.Day1)
                 from CodeTable b
                where b.Code = a.Code
               having count(*) = count(Day1));

解説

分析関数を使わない方法では、nullの有無を
having count(*) = count(Day1)
で判定してます。

5-40 グループごとに最大値の行を取得