5-53 前後の行も出力

SQLパズル

Logテーブル
ID         day
--  ----------
 1  2006/11/10
 2  2006/11/09
 4  2006/11/03
 3  2006/11/03
 6  2006/10/10
 5  2006/10/09

Logテーブルを
order by day desc,ID desc
でソートした時の、
IDが3の行と、前後の行を出力する。

出力結果
ID         day
--  ----------
 4  2006/11/03
 3  2006/11/03
 6  2006/10/10

データ作成スクリプト

create table Log(
ID  number(1),
day date);

insert into Log values(1,to_date('2006/11/10','yyyy/mm/dd'));
insert into Log values(2,to_date('2006/11/09','yyyy/mm/dd'));
insert into Log values(4,to_date('2006/11/03','yyyy/mm/dd'));
insert into Log values(3,to_date('2006/11/03','yyyy/mm/dd'));
insert into Log values(6,to_date('2006/10/10','yyyy/mm/dd'));
insert into Log values(5,to_date('2006/10/09','yyyy/mm/dd'));
commit;

SQL

--■■■分析関数を使う方法1■■■
select ID,day
from (select ID,day,
      count(decode(ID,3,1))
      over(order by day desc,ID desc
           rows between 1 preceding
                    and 1 following) as cnt
      from Log)
where cnt != 0;

--■■■分析関数を使う方法2■■■
select ID,to_char(day,'yyyy/mm/dd') as day
from (select ID,day,
      Lead(ID) over(order by day desc,ID desc) as LeadID,
      Lag(ID)  over(order by day desc,ID desc) as LagID
        from Log)
 where 3 in(LeadID,ID,LagID)
order by day desc,ID desc;

--■■■unionを使う方法■■■
select ID,to_char(day,'yyyy/mm/dd') as day
from (select a.ID,a.day
        from Log a,(select ID,day from Log where ID = 3) b
       where a.day < b.day
          or a.day = b.day and a.ID <= b.ID
      order by day desc,ID desc)
where RowNum <= 2
union
select ID,to_char(day,'yyyy/mm/dd')
from (select a.ID,a.day
        from Log a,(select ID,day from Log where ID = 3) b
       where a.day > b.day
          or a.day = b.day and a.ID >= b.ID
      order by day asc,ID asc)
where RowNum <= 2
order by day desc,ID desc;

--■■■union allを使う方法■■■
select ID,to_char(day,'yyyy/mm/dd') as day
from (select a.ID,a.day
        from Log a,(select ID,day from Log where ID = 3) b
       where a.day < b.day
          or a.day = b.day and a.ID < b.ID
      order by day desc,ID desc)
where RowNum = 1
union all
select ID,to_char(day,'yyyy/mm/dd')
from (select a.ID,a.day
        from Log a,(select ID,day from Log where ID = 3) b
       where a.day > b.day
          or a.day = b.day and a.ID >= b.ID
      order by day asc,ID asc)
where RowNum <= 2
order by day desc,ID desc;

解説

ascとdescを入れ替えたソートを、逆ソート(リバースソート)といいます。
例1 order by a,b,cの逆ソートは、
    order by a desc,b desc,c desc
例2 order by a desc,b,c ascの逆ソートは、
    order by a asc,b desc,c desc

unionを使う方法では、
order by day desc,ID descの逆ソート
つまり
order by day asc,ID asc
というのを使ってます。