CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
N.TheQuick
N.TheQuick

Reputation: 13

Find missing gaps in table records

I have a list of table record of this kind:

20180108001
20180108002
20180108003
20180108004

and so on

At a certain point this sequence broke up like this

20180108099
20180108102

missing 100 and 101 record

Is there a "select" command to extract the first NON sequential record?

Upvotes: 0

Views: 113

Answers (6)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can use sub-query and self join

select min(t.id) from t
where t.id  in (

   select t1.id from t t1
          left  join t t2 on t1.id=t2.id+1
          where t2.id is null
               )

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

This is a Gaps and Islands problem. make row number by id, then get sequential record group.

then use aggregate function get MIN and MAX id from those group.

  • MIN(ID) -1 NON sequential record end
  • MAX(ID) + 1 NON sequential record start.

final use LEAD to get the next NON-sequential record start value.

You can try this.

with cte as (
    SELECT MIN(ID) -1  startnum,MAX(ID) +1 endnum
    FROM (
       SELECT  id,id - ROW_NUMBER() OVER(ORDER BY id) grp
       FROM T
    ) t1
    group by grp
)

SELECT endnum from_gap,to_gap 
FROM (
    select *,lead(startnum) over(order by startnum) to_gap
    from cte
) t1
where t1.to_gap is not null

Upvotes: 0

Thomas G
Thomas G

Reputation: 10206

Here's an alternative way to list all the "holes" for a certain date :

SELECT x AS gap_from, next_x AS gap_to
  FROM ( SELECT x, LEAD(x) OVER (ORDER BY x) next_x
            FROM ( SELECT SUBSTR(the_column,-4,3) AS x 
                         FROM the_table 
                         WHERE the_column LIKE '201808%'
                        )
  )
  WHERE x <> next_x-1

It will return :

gap_from | gap_to
-----------------
099      | 102
etc...

Upvotes: 2

jarlh
jarlh

Reputation: 44766

To return 20180108100, select the previous row and add 1.

select min(col + 1)
from tablename
where col + 1 not in (select col from tablename)

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() :

select t.* 
from (select t.*, id - (row_number() over (order by id)) as seq
      from table t
     ) t
where seq > 0;

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use lag() function, like here:

with t (a) as (
  select 20180107099 from dual union all
  select 20180108002 from dual union all
  select 20180108003 from dual union all
  select 20180108004 from dual )
select a from (select a, lag(a) over(order by a) + 1 as lg from t)
  where lg <> a

Upvotes: 1

Related Questions