How to identify missing records in a single-column table?

hi,

try this (return intervals of missing ids):


select case when id - prev_id = 2 then to_char( id +1)

else '[' || nvl( prev_id +1 ,1 ) || ' , ' || (id-1) || ']'

end as missing_range

, id - nvl(prev_id,1) -1 number_count

from

(

select id

, lag(id) over (partition by 1 order by id ) as prev_id

from table_name

)

where id != prev_id +1

or prev_id is null

Code
CREATE TABLE t_missing (SL NUMBER);
 
SELECT * FROM t_missing
 
SELECT nxt FROM (
SELECT a.sl,b.sl,b.nxt,b.prv FROM t_missing a,(SELECT sl,sl+1 nxt,sl-1 prv FROM t_missing) b
WHERE b.sl = a.sl)
WHERE NOT EXISTS (SELECT 1 FROM t_missing WHERE sl = nxt)
AND   nxt < = (SELECT MAX(sl) FROM t_missing)
 

there was bug in first line (must be prev_id instead id):

Code
SELECT CASE WHEN id - prev_id = 2 THEN TO_CHAR( prev_id +1)  
ELSE '[' || NVL( prev_id +1 ,1 ) || ' , ' || (id-1) || ']'  
END AS missing_range , id - NVL(prev_id,1) -1 number_count 
FROM ( SELECT id  , LAG(id) over (PARTITION BY 1 ORDER BY id ) AS prev_id FROM table_name ) 
WHERE id != prev_id +1 OR prev_id IS NULL 

Code
WITH num_table AS
(
SELECT 1 AS NM FROM dual UNION ALL
SELECT 3 FROM dual UNION ALL
SELECT 4 FROM dual UNION ALL
SELECT 7 FROM dual UNION ALL
SELECT 9 FROM dual
)
SELECT LEVEL FROM dual CONNECT BY LEVEL <= (SELECT MAX(NM) FROM num_table)
MINUS 
SELECT NM FROM num_table

Code
SELECT LEVEL FROM dual
CONNECT BY LEVEL <=10
MINUS
SELECT a FROM test
 

where the test table is like colA 2 4 6 7 the result will be the missing numbers..

Code
SELECT 
           CASE WHEN ConNum - prev_id = 2 THEN TO_CHAR( prev_id +1)  
           ELSE '[' || NVL( prev_id +1 ,1 ) || ' , ' || (ConNum-1) || ']' 
 END AS missing_range , ConNum - NVL(prev_id,1) -1 number_count 
FROM 
( SELECT ConNum  , nvl(LAG(ConNum) over (PARTITION BY 1 ORDER BY ConNum ),0) AS prev_id FROM SingleColeTab ) 
WHERE ConNum != prev_id +1 OR prev_id IS NULL