5-63 order by句で分析関数その2

SQLパズル

fruitTable
fruit   day1
------  ----------
grape   2008-01-05
apple   2008-02-15
orange  2008-02-18
apple   2008-04-11
orange  2008-05-02
apple   2008-06-23
grape   2008-07-05

day1が最新のfruitから出力する(同じfruitは連続して出力)

出力結果
fruit   day1
------  ----------
grape   2008-07-05
grape   2008-01-05
apple   2008-06-23
apple   2008-04-11
apple   2008-02-15
orange  2008-05-02
orange  2008-02-18

データ作成スクリプト

create table fruitTable(fruit,day1) as
select 'grape' ,date '2008-01-05' from dual union
select 'apple' ,date '2008-02-15' from dual union
select 'orange',date '2008-02-18' from dual union
select 'apple' ,date '2008-04-11' from dual union
select 'orange',date '2008-05-02' from dual union
select 'apple' ,date '2008-06-23' from dual union
select 'grape' ,date '2008-07-05' from dual;

SQL

--■■■分析関数を使う方法■■■
select fruit,day1
  from fruitTable
order by max(day1) over(partition by fruit) desc,day1 desc;

--■■■相関サブクエリを使う方法■■■
select fruit,day1
  from fruitTable a
order by (select max(b.day1)
            from fruitTable b
           where b.fruit = a.fruit) desc,day1 desc;

解説

order by句で、分析関数を使うことができます。
5-52 order by句で分析関数の類題と言えるでしょう。

---