7-31 最も近い日を取得

SQLパズル

Dayテーブルから、
現在日(sysdate)に最も近い日を取得する。

データ作成スクリプト

create table DayTable(day date);

insert into DayTable values(sysdate-6);
insert into DayTable values(sysdate-2);
insert into DayTable values(sysdate+2);
insert into DayTable values(sysdate+6);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select day from DayTable a
 where abs(trunc(a.day)-trunc(sysdate)) =
(select min(abs(trunc(b.day)-trunc(sysdate)))
   from DayTable b);

--■■■分析関数を使う方法■■■
select day
from (select day,
          abs(trunc(day)-trunc(sysdate)) as Diff,
      min(abs(trunc(day)-trunc(sysdate))) over() as MinDiff
      from DayTable)
where Diff = MinDiff;

--■■■rank関数を使う方法■■■
select day
from (select day,
      rank() over(order by abs(trunc(day)-trunc(sysdate))) as Rn
        from DayTable)
 where Rn =1;

解説

trunc関数を使って、時間情報を切り捨てて、
abs関数を使って、日付の差を求めてます。

---