Tags

,

One of the things often asked for is how to find gaps in a list of numbers. So here’s a rather old suggestion:

WITH gaps (id) AS (SELECT  1 FROM dual UNION ALL
                   SELECT  3 FROM dual UNION ALL
                   SELECT  4 FROM dual UNION ALL
                   SELECT  7 FROM dual UNION ALL
                   SELECT 15 FROM dual)
    ,borders AS (SELECT MIN(id) mins
                       ,MAX(id) maxs
                   FROM gaps)
SELECT mins + (ROWNUM - 1) AS missing_value
  FROM borders
CONNECT BY mins + (ROWNUM - 1) <> maxs
MINUS
SELECT id
  FROM gaps
/