10-111 最新日で最大金額の行を取得

SQLパズル

売上テーブル
コード  日付        金額  商品名
------  ---------  ----  ------
   100  2006/4/19   100  商品あ
   100  2006/4/19    50  商品い
   100  2006/4/20   200  商品う  ←出力対象
   200  2006/4/19   200  商品え  ←出力対象
   300  2006/4/18   100  商品お
   300  2006/4/19   300  商品か
   300  2006/4/19   600  商品き  ←出力対象
   400  2006/4/19   800  商品く
   400  2006/4/20   100  商品け
   400  2006/4/20   200  商品こ  ←出力対象
   500  2006/5/19   100  商品さ
   500  2006/5/20   900  商品し  ←出力対象
   500  2006/5/20   900  商品す  ←出力対象
   600  2006/5/20   900  商品せ  ←出力対象
   600  2006/5/20   900  商品そ  ←出力対象

コードごとの、
日付が最新で、金額が最大のレコードを出力する。

出力結果
コード  日付        金額  商品名
------  ---------  ----  ------
   100  2006/4/20   200  商品う
   200  2006/4/19   200  商品え
   300  2006/4/19   600  商品き
   400  2006/4/20   200  商品こ
   500  2006/5/20   900  商品し
   500  2006/5/20   900  商品す
   600  2006/5/21   900  商品せ
   600  2006/5/21   900  商品そ

データ作成スクリプト

create table 売上(
コード char(3),
日付   date,
金額   number(3),
商品名 char(6));

insert into 売上 values(100,to_date('2006/4/19','fmyyyy/mm/dd'),100,'商品あ');
insert into 売上 values(100,to_date('2006/4/19','fmyyyy/mm/dd'), 50,'商品い');
insert into 売上 values(100,to_date('2006/4/20','fmyyyy/mm/dd'),200,'商品う');
insert into 売上 values(200,to_date('2006/4/19','fmyyyy/mm/dd'),200,'商品え');
insert into 売上 values(300,to_date('2006/4/18','fmyyyy/mm/dd'),100,'商品お');
insert into 売上 values(300,to_date('2006/4/19','fmyyyy/mm/dd'),300,'商品か');
insert into 売上 values(300,to_date('2006/4/19','fmyyyy/mm/dd'),600,'商品き');
insert into 売上 values(400,to_date('2006/4/19','fmyyyy/mm/dd'),800,'商品く');
insert into 売上 values(400,to_date('2006/4/20','fmyyyy/mm/dd'),100,'商品け');
insert into 売上 values(400,to_date('2006/4/20','fmyyyy/mm/dd'),200,'商品こ');
insert into 売上 values(500,to_date('2006/5/19','fmyyyy/mm/dd'),100,'商品さ');
insert into 売上 values(500,to_date('2006/5/20','fmyyyy/mm/dd'),900,'商品し');
insert into 売上 values(500,to_date('2006/5/20','fmyyyy/mm/dd'),900,'商品す');
insert into 売上 values(600,to_date('2006/5/21','fmyyyy/mm/dd'),900,'商品せ');
insert into 売上 values(600,to_date('2006/5/21','fmyyyy/mm/dd'),900,'商品そ');
commit;

SQL

--■■■非相関サブクエリを使う方法■■■
select コード,日付,金額,商品名
  from 売上 a
 where (コード,日付,金額) in(select b.コード,max(b.日付),
                             max(b.金額) keep(dense_rank Last order by b.日付)
                             from 売上 b
                             group by b.コード);

--■■■相関サブクエリを使う方法■■■
select コード,日付,金額,商品名
  from 売上 a
 where exists(select 1 from 売上 b
               where b.コード = a.コード
              having max(b.日付) = a.日付
                 and max(b.金額) keep(dense_rank Last order by b.日付) = a.金額);

--■■■分析関数を使う方法1■■■
select コード,日付,金額,商品名
  from (select コード,日付,金額,商品名,
        max(日付) over(partition by コード) as max日付,
        max(金額) over(partition by コード,日付) as max金額
        from 売上)
 where 日付 = max日付
   and 金額 = max金額;

--■■■分析関数を使う方法2■■■
select コード,日付,金額,商品名
  from (select コード,日付,金額,商品名,
        Rank() over(partition by コード order by 日付 desc,金額 desc) as Rank
        from 売上)
 where Rank = 1;

解説

分析関数を使う方法では、
max関数で最大値を求める方法や、
Rank関数で順位を求める方法があります。

---