Finding missing sequence in a table

Question

Hello all.

I'm using the Oracle 10g Database. i'm trying to figure out how to write a simple sql query to:

find the missing numbers in a table between say 86002895 and 86005197 (inclusive), There are 1955 rows between 86002895 and 86005197.

Ex: Current Scenario : table_1 :

tracking_no | id_value
86002895 | 10
86002896 | 10
86002899 | 10
86002900 | 10
86002910 | 10
86005196 | 10
86005197 | 10

Expected Result1:

" missing tracking_id " where id_value = 10 from table_1 ;

86002897

86002898
86002900 to

86002910

86002910 to

86005196

Thanks in advance

Answer 1

If you had a numbers table, you would do this:

SELECT t.min_no+n.Number-1 AS missing_no
FROM Numbers n
INNER JOIN (
  SELECT MIN(tracking_no) AS min_no, MAX(tracking_no) AS max_no 
  FROM TABLE WHERE id_value = 10
  ) t ON n.Number BETWEEN 1 AND t.max_no-t.min_no+1
WHERE n.Number+t.min_no-1 NOT IN (
  SELECT tracking_no FROM TABLE
  WHERE id_value = 10
  );

A numbers table is a table with one integer column, with numbers from 0 or 1 to however high you need.

Answer 2

Try a cursor? Not a complete solution...

declare
    V_IDX   number := 86002895;
begin
    for REC in (select   *
                from     TABLE_1
                order by TRACKING_NO asc)
    loop
        if V_IDX <> REC.TRACKING_NO then
            dbms_output.PUT_LINE('missing tracking_id '|| REC.TRACKING_NO || ' where id_value = ' || REC.ID_VALUE || ' from table_1');
        end if;
        V_IDX := V_IDX + 1;
    end loop;
end;

Update: I can't yet add a comment but in addition to Peter's answer you can make a numbers table on the fly. For example the following will return all numbers between 86002895 and 86004849 inclusive:

select rownum+86002895-1
from dual
connect by level <= 1955

Answer 3

Not the most elegant solution, but it works (in MySQL - I don't use Oracle, so I hope it works for you!):

SELECT tracking_no
FROM yourtable
WHERE id_value = 10
AND tracking_no-1 NOT IN (SELECT tracking_no FROM yourtable WHERE id_value=10)

UNION

SELECT tracking_no
FROM yourtable
WHERE id_value = 10
AND tracking_no+1 NOT IN (SELECT tracking_no FROM yourtable WHERE id_value=10)

ORDER BY tracking_no

Answer 4

with data as 
(
  select tracking_no from table_1 where id_value = 10
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);

If you want to include 86002895 and 86005197 do:

with data as 
(
  select tracking_no from table_1 
  where id_value = 10
  and   tracking_no between 86002895 and 86005197
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);

Answer 5

The following approach gives you a list of all missing tracking numbers but not there ranges:

Step 1: find Max and Min values for tracking_no: M1 and M2

Step 2: create temporary table TempNumbers containing single column tracking_no

Step 3: insert (M2 - M1) + 1 unique rows into TempNumbers with values from M1 to M2 inclusively

Step 4:

SELECT tracking_no FROM TempNumbers 
 WHERE NOT EXISTS (SELECT 'not found' FROM table_1 
                    WHERE TempNumbers.tracking_no = table_1.tracking_no)

Answer 6

Use MINUS set operation.

-- all numbers
SELECT ROWNUM
FROM dual
CONNECT BY level <= :SOME_LARGE_VALUE_HERE
MINUS
-- some numbers missing
SELECT id 
FROM table_1

Tweak as needed.

Answer 7

This returns a list of the start-end of each missing range:

select s, e from
(select s, rownum sr
from
(
select tracking_no + 1 s
from table_1
where id_value = 10
MINUS
select tracking_no
from table_1
where id_value = 10
order by s
)),
(
select e, rownum er
from
(
select tracking_no - 1 e
from table_1
where id_value = 10
MINUS
select tracking_no
from table_1
where id_value = 10
order by e
))
where er-1 = sr;

Answer 8

 select  next_in_sequence missing_range_begin, 
    next_actual -1  missing_range_end
    from
    (
    select
    tracking_no, 
    tracking_no + 1 next_in_sequence,
    lead(tracking_no, 1) over (order by tracking_no) next_actual
    from test
    where id_value = 10
    order by tracking_no
    )
    where next_in_sequence <> next_actual
    ;

Answer 9

Basically use 86002894 as an offset. Then it becomes a simple query. If your table is really big you can add a where clause.

I've used ALL_OBJECTS which works in this case but you can use any table that has sufficient number of rows in it.

SELECT rownum+86002894
FROM All_Objects
WHERE rownum between 1 AND 86005197-86002895
MINUS
SELECT tracking_no
FROM your_table

Answer 10

A solution with the model clause:

select rangech
from
(
  select rangech
  from      table_1
  where  id_value = 10
  model
  dimension by (row_number() over (order by tracking_no) rn)
  measures (cast(null as varchar2(25)) rangech,tracking_no no)
  rules
  (
    rangech[any] = case
                   when no[cv()+1] is not null and no[cv()]+1 < no[cv()+1]-1
                        then to_char(no[cv()]+1)||'-'||to_char(no[cv()+1]-1)
                   when no[cv()+1] is not null and no[cv()]+1 = no[cv()+1]-1
                        then to_char(no[cv()]+1)
                   else
                        'X'
                   end
  )
)
where rangech <> 'X'
order by rangech;

Output:

RANGECH
-------------------------
86002897-86002898
86002901-86002909
86002911-86005195