10-1 2番目のデータを取得

SQLパズル

sampleテーブル
name       entry_date
--------   ----------
毛利元就     1497/1/1
毛利元就     1555/1/1
毛利元就     1555/1/1
織田信長     1534/1/1
織田信長     1560/1/1
織田信長     1560/1/1
織田信長     1582/1/1
織田信長         null
明智光秀     1582/1/1
明智光秀     1582/1/1
豊臣秀吉     1536/1/1
豊臣秀吉     1597/1/1
豊臣秀吉     1597/1/1
前田利家     1599/1/1
徳川家康     1560/1/1
徳川家康     1600/1/1
徳川家康     1615/1/1
服部半蔵         null
服部半蔵         null

sampleテーブルから、
nameごとにentry_dateが2番目に新しいレコードを抽出する。
entry_dateが2番目のデータが複数存在する場合は、どれか1件のみ取得する。
entry_dateがnullのレコードは対象外とする。

出力結果
name       entry_date
--------   ----------
毛利元就     1555/1/1
織田信長     1560/1/1
明智光秀     1582/1/1
豊臣秀吉     1597/1/1
徳川家康     1600/1/1

データ作成スクリプト

create table sample(
name       char(8),
entry_date date);

insert into sample values('毛利元就',to_date('14970101','yyyymmdd'));
insert into sample values('毛利元就',to_date('15550101','yyyymmdd'));
insert into sample values('毛利元就',to_date('15550101','yyyymmdd'));
insert into sample values('織田信長',to_date('15340101','yyyymmdd'));
insert into sample values('織田信長',to_date('15600101','yyyymmdd'));
insert into sample values('織田信長',to_date('15600101','yyyymmdd'));
insert into sample values('織田信長',to_date('15820101','yyyymmdd'));
insert into sample values('織田信長',null');
insert into sample values('明智光秀',to_date('15820101','yyyymmdd'));
insert into sample values('明智光秀',to_date('15820101','yyyymmdd'));
insert into sample values('豊臣秀吉',to_date('15360101','yyyymmdd'));
insert into sample values('豊臣秀吉',to_date('15970101','yyyymmdd'));
insert into sample values('豊臣秀吉',to_date('15970101','yyyymmdd'));
insert into sample values('前田利家',to_date('15990101','yyyymmdd'));
insert into sample values('徳川家康',to_date('15600101','yyyymmdd'));
insert into sample values('徳川家康',to_date('16000101','yyyymmdd'));
insert into sample values('徳川家康',to_date('16150101','yyyymmdd'));
insert into sample values('服部半蔵',null);
insert into sample values('服部半蔵',null);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select name,to_char(entry_date,'fmyyyy/mm/dd') as entry_date from sample a
where (select count(*) from sample b
        where b.name=a.name
          and (b.entry_date > a.entry_date
            or b.entry_date = a.entry_date and  b.RowID > a.RowID)) = 1
order by entry_date;

--■■■分析関数を使う方法■■■
select name,to_char(entry_date,'fmyyyy/mm/dd') as entry_date
from (select name,entry_date,
             Row_Number() over(partition by name order by entry_date desc) as Rank
        from sample
       where entry_date is not null)
where Rank=2
order by entry_date;

解説

相関サブクエリを使う方法では、
entry_dateが大きいか、
entry_dateが等しい場合は、RowIDが大きいかチェックして、
順位が上のレコードの件数を取得してます。

分析関数を使う方法もあります。