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


  Was this answer useful?  Yes

kikilaki

  • Jul 21st, 2011
 

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

Code
  1. span style="color: #F00;">'['' , ' || (id-1) || ']'

  Was this answer useful?  Yes

geetha

  • Aug 25th, 2011
 

Code
  1.  


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. span style="color: #ff0000;">'[' || NVL( prev_id +1 ,1 ) || ' , '']'

  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