Series: Subject: Topic:
Question: 26 of 279

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

How to identify missing records in a single-column table ?
Column consists of numbers in a ordered manner but the some numbers are deleted from the table in random manner and need to identify those rows.
p.s. This table consist of only 1 row.
Asked by: sandip.dhopat | Member Since Jan-2011 | Asked on: Jun 6th, 2011
kikilaki

Answered On : Jun 7th, 2011

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

1 User has rated as useful.

Sudhakara

Answered On : Jul 19th, 2011

```CodeCREATE 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)
```

kikilaki

Answered On : Jul 21st, 2011

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

```CodeSELECT 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 ```

GoroAU

Answered On : Aug 17th, 2011

```CodeWITH 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```

geetha

Answered On : Aug 25th, 2011

```CodeSELECT 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..

Satya

Answered On : Sep 20th, 2011

```CodeSELECT
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 ```