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

SQLパズル

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

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

出力結果
No
--
 5

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
以下のような、データに欠番がないパターンも考慮する。

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

出力結果
No
--
 7

データ作成スクリプト

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(5);
insert into GetNo values(6);
commit;

SQL

--■■■Row_Number関数を使う方法1■■■
select nvl(min(Rank),1) as No
  from (select case No
               when max(No) over() then No+1
               else No end as 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から開始される連番であれば、
順位と比較する方法が使えます。

データに欠番がないパターンを考慮するので、
Row_Number関数を使う方法1では、
最大のNoは、(最大のNO+1)
とすることによる番兵法
を使ってます。

Row_Number関数を使う方法2のように、
発想を変えた方法もあります。

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

OTN-Japan varchar2項目の空き番号を取得
OTN-Japan 項目の値だけでグループ化できないまとまりの集計方法

---

分析関数の衝撃1 (前編)
分析関数の衝撃5 (総集編)

---