2-3-27 最小の空き番号を取得その1

SQLパズル

GetNoテーブル
No
--
 1
 2
 3
 4
 6
 7
 9

1から開始される連番の、空き番号の最小値を求める。
GetNoテーブルのプライマリキーは、Noとする。

データに欠番がないパターンは、考慮しない

出力結果
No
--
 5

データ作成スクリプト

create table GetNo(No number(2) primary key);

insert into GetNo values(1);
insert into GetNo values(2);
insert into GetNo values(3);
insert into GetNo values(4);
insert into GetNo values(6);
insert into GetNo values(7);
insert into GetNo values(9);
commit;

SQL

--■■■インラインビューでソートする方法■■■
select nvl(min(Rank),1) as No
  from (select No,RowNum as Rank
          from (select No
                  from GetNo
                order by No))
 where Rank < No;

--■■■Row_Number関数を使う方法1■■■
select nvl(min(Rank),1) as No
  from (select No,Row_Number() over(order by No) as Rank
          from GetNo)
 where Rank < No;

--■■■Row_Number関数を使う方法2■■■
select nvl(max(No),0)+1 as No
from (select No,Row_Number() over(order by No) as Rank
        from GetNo)
where No = Rank

--■■■Lead関数を使う方法1■■■
select nvl(min(No+1),1) as No
from (select No,Lead(No) over(order by No) as LeadNo
        from GetNo)
where No+1 != LeadNo
   or LeadNo is null;

--■■■Lead関数を使う方法2(10g以降)■■■
select nvl(min(No+1),1) as No
from (select No,Lead(No) over(order by No) as LeadNo
        from GetNo)
where lnnvl(No+1 = LeadNo);

解説

1から開始される連番であれば、
順位と比較する方法が使えます。

2-3-1 自己結合と相関サブクエリ
2-3-28 最小の空き番号を取得その2

---

CodeZine：分析関数の衝撃（前編）

---