10-236 最もbetterな行を出力

SQLパズル

PointTable
Name  Col1
----  -----
AAAA  Bad
AAAA  Good
AAAA  Best
BBBB  Good
CCCC  Bad
DDDD  Bad
DDDD  Best
EEEE  Best
EEEE  Worst
FFFF  Bad
FFFF  Worst

Nameごとの、Col1が最もbetterな行を出力する。

betterなほうから順に
Best,Good,Bad,Worst
となります。

出力結果
Name  Col1
----  -----
A     Best
B     Good
C     Bad
D     Best
E     Best
F     Bad

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table PointTable(Name,Col1) as
select 'AAAA','Bad'   from dual union all
select 'AAAA','Good'  from dual union all
select 'AAAA','Best'  from dual union all
select 'BBBB','Good'  from dual union all
select 'CCCC','Bad'   from dual union all
select 'DDDD','Bad'   from dual union all
select 'DDDD','Best'  from dual union all
select 'EEEE','Best'  from dual union all
select 'EEEE','Worst' from dual union all
select 'FFFF','Bad'   from dual union all
select 'FFFF','Worst' from dual;

SQL

--■■■分析関数を使う方法1■■■
select Name,Col1
from (select Name,Col1,
      rank() over(partition by Name
                  order by case Col1
                           when 'Best'  then 1
                           when 'Good'  then 2
                           when 'Bad'   then 3
                           when 'Worst' then 4 end) as Rn
      from PointTable)
where Rn = 1
order by Name;

--■■■分析関数を使う方法2■■■
select distinct Name,
First_Value(Col1)
over(partition by Name
order by case Col1
         when 'Best'  then 1
         when 'Good'  then 2
         when 'Bad'   then 3
         when 'Worst' then 4 end) as Col1
      from PointTable
order by Name;

解説

単純case式で、順位に変換してます。

---