script to identify gaps inside a list of numbers

[tpanzer]

How can I easily identify in a list of numbers
which numbers are missing.

Input: table with list of numbers (number(1) - Number )

Output: numbers which are not in the list

Cirteria: a number is missing,
when (Number( i+1) - Number(i)) > 1

Example: List : {20, 21, 23, 24, 28, 29,30}

Output: Missing are the numbers 22, 25, 26, 27

 

[r937]

easily? create an integers table which contains all the integers, and do a LEFT OUTER JOIN



you can determine where the gaps are like this:

select t1.i + 1 as missing
  from numbers t1
left outer
  join numbers t2
    on t1.i + 1 = t2.i
 where t1.i 
     < (select max(i) from numbers)
   and t2.i is null

note, however, that this will return only 22 and 25, i.e. it will detect only the first missing number (25) in a gap of several missing numbers (25, 26, 27)

to itemize all the missing numbers, you'll need that LEFT OUTER JOIN

r937.com | rudy.ca

 

[dnoeth]

Hi Tpanzer,
do you really need 25, 26 and 27 in separate rows?

Or is one row with start and end of the missing range of numbers is enough?

select
n + 1 as "from",
nextn - 1 as "to",
nextn - n as "cnt"
from
(
select
n,
(select min from numbers n2
where n2.n > n.n) as nextn
from numbers n
) dt
where nextn - n > 1


If your DBMS already supports ANSI OLAP functions:

select
n + 1 as "from",
nextn - 1 as "to",
nextn - n as "cnt"
from
(
select
n,
min over (order by n
rows between 1 following and 1 following) as nextn
from numbers
) dt
where nextn - n > 1

Untested...

Dieter