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.

Questions by sandip.dhopat   answers by sandip.dhopat

Showing Answers 1 - 14 of 14 Answers

kikilaki

  • 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

Sudhakara

  • Jul 19th, 2011
 

Code
  1. CREATE TABLE t_missing (SL NUMBER);

  2.  

  3. SELECT * FROM t_missing

  4.  

  5. SELECT nxt FROM (

  6. 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

  7. WHERE b.sl = a.sl)

  8. WHERE NOT EXISTS (SELECT 1 FROM t_missing WHERE sl = nxt)

  9. AND   nxt < = (SELECT MAX(sl) FROM t_missing)

  10.  


  Was this answer useful?  Yes

GoroAU

  • Aug 17th, 2011
 


Code
  1. WITH num_table AS

  2. (

  3. SELECT 1 AS NM FROM dual UNION ALL

  4. SELECT 3 FROM dual UNION ALL

  5. SELECT 4 FROM dual UNION ALL

  6. SELECT 7 FROM dual UNION ALL

  7. SELECT 9 FROM dual

  8. )

  9. SELECT LEVEL FROM dual CONNECT BY LEVEL <= (SELECT MAX(NM) FROM num_table)

  10. MINUS

  11. SELECT NM FROM num_table

  Was this answer useful?  Yes

geetha

  • Aug 25th, 2011
 

Code
  1. SELECT LEVEL FROM dual

  2. CONNECT BY LEVEL <=10

  3. MINUS

  4. SELECT a FROM test

  5.  


where the test table is like

colA
2
4
6
7
the result will be the missing numbers..

  Was this answer useful?  Yes

Satya

  • Sep 20th, 2011
 

Code
  1. SELECT

  2.            CASE WHEN ConNum - prev_id = 2 THEN TO_CHAR( prev_id +1)  

  3.            ELSE '[' || NVL( prev_id +1 ,1 ) || ' , ' || (ConNum-1) || ']'

  4.  END AS missing_range , ConNum - NVL(prev_id,1) -1 number_count

  5. FROM

  6. ( SELECT ConNum  , nvl(LAG(ConNum) over (PARTITION BY 1 ORDER BY ConNum ),0) AS prev_id FROM SingleColeTab )

  7. WHERE ConNum != prev_id +1 OR prev_id IS NULL

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions