10-47 条件を満たす最大のデータを取得

SQLパズル

ID  連番  名称       発効年月日  失効年月日
--  ----  --------  ----------  ----------
A1     1  ○○商店   19800401    20011231
A2     1  △△商事   19900401    99991231
A3     1  □□商会   20000401    20031231
A3     2  □□       20040101    99991231
B1     1  ××興業   20041001    99991231
B2     1  ○○組     20011001    99991231
B2     2  ○△○△   20040401    99991231

IDごとの、sysdateが発効年月日以上、失効年月日以下のデータの中で
連番が最大のデータを出力する

出力結果
ID  連番  名称       発効年月日  失効年月日
--  ----  --------  ----------  ----------
A2     1  △△商事   19900401    99991231
A3     2  □□       20040101    99991231
B1     1  ××興業   20041001    99991231
B2     2  ○△○△   20040401    99991231

データ作成スクリプト

create table マスタ(
ID char(2),
連番 number(1),
名称 varchar2(8),
発効年月日 char(10),
失効年月日 char(10));

insert into マスタ values('A1',1,'○○商店','19800401','20011231');
insert into マスタ values('A2',1,'△△商事','19900401','99991231');
insert into マスタ values('A3',1,'□□商会','20000401','20031231');
insert into マスタ values('A3',2,'□□'    ,'20040101','99991231');
insert into マスタ values('B1',1,'××興業','20041001','99991231');
insert into マスタ values('B2',1,'○○組'  ,'20011001','99991231');
insert into マスタ values('B2',2,'○△○△','20040401','99991231');
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select ID,連番,名称,発効年月日,失効年月日
from マスタ a
where 連番 = (select max(b.連番) from マスタ b
               where b.ID = a.ID
                 and to_char(sysdate,'YYYYMMDD') between b.発効年月日 and b.失効年月日)
order by ID;

--■■■分析関数を使う方法1■■■
select ID,連番,名称,発効年月日,失効年月日
from (select ID,連番,名称,発効年月日,失効年月日,
      Row_Number() over(partition by ID order by 連番 desc) as Rank
      from マスタ
      where to_char(sysdate,'YYYYMMDD') between 発効年月日 and 失効年月日)
where Rank = 1
order by ID;

--■■■分析関数を使う方法2■■■
select distinct ID,
max(連番) over(partition by ID) as 連番,
Last_Value(名称) over(partition by ID order by 連番
Rows between Unbounded Preceding and Unbounded Following) as 名称,
Last_Value(発効年月日) over(partition by ID order by 連番
Rows between Unbounded Preceding and Unbounded Following) as 発効年月日,
Last_Value(失効年月日) over(partition by ID order by 連番
Rows between Unbounded Preceding and Unbounded Following) as 失効年月日
from マスタ
where to_char(sysdate,'YYYYMMDD') between 発効年月日 and 失効年月日
order by ID;

解説

分析関数を使う方法2では、
distinctと分析関数を組み合わせてます

---