How to check any missing number from a series of numbers?

Question

I am doing a project creating an admission system for a college; the technologies are Java and Oracle.

In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant's form data will be entered. My requirement is that when the entry process is completed I will have to generate a Lot wise report. If during feeding pre-generated serial numbers any sequence numbers went missing.

For example, say in a table, the sequence numbers are 7001, 7002, 7004, 7005, 7006, 7010. From the above series it is clear that from 7001 to 7010 the numbers missing are 7003, 7007, 7008 and 7009

Is there any DBMS function available in Oracle to find out these numbers or if any stored procedure may fulfill my purpose then please suggest an algorithm.

I can find some techniques in Java but for speed I want to find the solution in Oracle.

Thermomotor

I added the gaps-and-islands tag. searching for it will probably yield a sufficient amount of prior art, including recursive queries. – Ferrate

See Find range of missing values in a sequence of numbers or dates – Negrillo

Answer 1

A solution without hardcoding the 9:

select min_a - 1 + level
     from ( select min(a) min_a
                 , max(a) max_a
              from test1
          )
  connect by level <= max_a - min_a + 1
    minus
   select a
     from test1

---

Results:

MIN_A-1+LEVEL
-------------
         7003
         7007
         7008
         7009

4 rows selected.

Palembang

That makes my answer look ridiculously over-complicated! +1 – Iveson

I was exploring the logic myself for a while and decided it was unnecessarily to waste time like this. I guess I should have make Google a good practice. Therefore +1 to this answer. – Inappreciative

what is LEVEL here? – Chlorate

Pseudocolumn LEVEL: docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/… – Palembang

Answer 2

Try this:

SELECT t1.SequenceNumber + 1 AS "From",
       MIN(t2.SequenceNumber) - 1 AS "To"
FROM MyTable t1
JOIN MyTable t2 ON t1.SequenceNumber < t2.SequenceNumber 
GROUP BY t1.SequenceNumber
HAVING t1.SequenceNumber + 1 < MIN(t2.SequenceNumber)

Here is the result for the sequence 7001, 7002, 7004, 7005, 7006, 7010:

From  To
7003  7003
7007  7009

Transmarine

Answer 3

This worked but selects the first sequence (start value) since it doesn't have predecessor. Tested in SQL Server but should work in Oracle

SELECT
    s.sequence  FROM seqs s
WHERE
    s.sequence - (SELECT sequence FROM seqs WHERE sequence = s.sequence-1) IS NULL

Here is a test result

  Table
  -------------
  7000
  7001
  7004
  7005
  7007
  7008

  Result
  ----------
  7000
  7004
  7007

To get unassigned sequence, just do value[i] - 1 where i is greater first row e.g. (7004 - 1 = 7003 and 7007 - 1 = 7006) which are available sequences

I think you can improve on this simple query

Holdover

This pre-supposes a table with all the sequences numbers stored in. There is no need to do this in Oracle. – Iveson

would Oracle's connect by perform better than this? – Holdover

the answer with the highest votes here uses 2 aggregate functions - what about performance? – Holdover

I like this simple solution, but if you have several missed numbers in sequence (7,8,9) it´ll only detect one of them – Aleida

Answer 4

This works on postgres >= 8.4. With some slight modifications to the CTE-syntax it could be made to work for oracle and microsoft, too.

-- EXPLAIN ANALYZE
WITH missing AS (
    WITH RECURSIVE fullhouse AS (
        SELECT MIN(num)+1 as num
        FROM numbers n0
        UNION ALL SELECT 1+ fh0.num AS num
        FROM fullhouse fh0
        WHERE EXISTS (
                SELECT * FROM numbers ex
                WHERE ex.num > fh0.num
                )
        )
        SELECT * FROM fullhouse fh1
        EXCEPT ( SELECT num FROM numbers nx)
        )
SELECT * FROM missing;

Ferrate

To the downvoter: please explain. This question is tagged 'sql' which is (should be) standard sql. CTE's are part of that. – Ferrate

It wasn't I who down-voted but to be fair to them it's also tagged Oracle and this syntax is incorrect. – Iveson

Well I've been told that the CTE is implemented in oracle see:https://mcmap.net/q/505297/-oracle-cte-merge. Of course the connect-by/prior construct has been around for a few years, but The CTE syntax is at least part of a standard, and there is always a reason for some diversity, even if it is standard. As I said in my reply: minor differences in syntax (such as omitting the RECURSIVE keyword) may exist. And finally: at least the query works for me (mebe with a few changes for others, too). There have been other answers posted here that dont work. – Ferrate

Answer 5

Here's a solution that:

• Relies on Oracle's LAG function
• Does not require knowledge of the complete sequence (but thus doesn't detect if very first or last numbers in sequence were missed)
• Lists the values surrounding the missing lists of numbers
• Lists the missing lists of numbers as contiguous groups (perhaps convenient for reporting)
• Tragically fails for very large lists of missing numbers, due to listagg limitations

SQL:

WITH MentionedValues /*this would just be your actual table, only defined here to provide data for this example */
        AS (SELECT *
              FROM (    SELECT LEVEL + 7000 seqnum
                          FROM DUAL
                    CONNECT BY LEVEL <= 10000)
             WHERE seqnum NOT IN (7003,7007,7008,7009)--omit those four per example
                                       ),
     Ranges /*identifies all ranges between adjacent rows*/
        AS (SELECT seqnum AS seqnum_curr,
                   LAG (seqnum, 1) OVER (ORDER BY seqnum) AS seqnum_prev,
                   seqnum - (LAG (seqnum, 1) OVER (ORDER BY seqnum)) AS diff
              FROM MentionedValues)
SELECT Ranges.*,
       (    SELECT LISTAGG (Ranges.seqnum_prev + LEVEL, ',') WITHIN GROUP (ORDER BY 1)
              FROM DUAL
        CONNECT BY LEVEL < Ranges.diff) "MissingValues" /*count from lower seqnum+1 up to lower_seqnum+(diff-1)*/
  FROM Ranges
 WHERE diff != 1 /*ignore when diff=1 because that means the numers are sequential without skipping any*/
;

Output:

SEQNUM_CURR SEQNUM_PREV DIFF MissingValues
7004        7002        2    "7003" 
7010        7006        4    "7007,7008,7009"                  

Catalonia

Answer 6

One simple way to get your answer for your scenario is this:

create table test1 ( a number(9,0));

insert into test1 values (7001);
insert into test1 values (7002);
insert into test1 values (7004);
insert into test1 values (7005);
insert into test1 values (7006);
insert into test1 values (7010);
commit;

select n.n from (select ROWNUM + 7001 as n from dual connect by level <= 9) n 
   left join test1 t on n.n = t.a where t.a is null;

The select will give you the answer from your example. This only makes sense, if you know in advance in which range your numbers are and the range should not too big. The first number must be the offset in the ROWNUM part and the length of the sequence is the limit to the level in the connect by part.

Eligible

You need to know that the value is 9. How do you know this? – Iveson

That's what I wrote: You need to know the range of your sequence. If I understand the task correctly, this is probably known. Or did I misunderstood you? – Eligible

Answer 7

I would have suggested connect by level as Stefan has done, however, you can't use a sub-query in this statement, which means that it isn't really suitable for you as you need to know what the maximum and minimum values of your sequence are.

I would suggest a pipe-lined table function might be the best way to generate the numbers you need to do the join. In order for this to work you'd need an object in your database to return the values to:

create or replace type t_num_array as table of number;

Then the function:

create or replace function generate_serial_nos return t_num_array pipelined is

   l_first number;
   l_last number;

begin

   select min(serial_no), max_serial_no)
     into l_first, l_last 
     from my_table
          ;

   for i in l_first .. l_last loop
      pipe row(i);
   end loop;

   return;

end generate_serial_nos;
/

Using this function the following would return a list of serial numbers, between the minimum and maximum.

select * from table(generate_serial_nos);

Which means that your query to find out which serial numbers are missing becomes:

select serial_no
  from ( select * 
           from table(generate_serial_nos) 
                ) generator 
  left outer join my_table actual
    on generator.column_value = actual.serial_no
 where actual.serial_no is null

Iveson

Answer 8

 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;

Ashantiashbaugh

Not downvoting because it does work if you change the values of a to 1, 2, 3...10, rather than the 7001-7010 noted by OP, but...well, it doesn't work if the numbers in question are 7001-7010 as noted in the question. dbfiddle here – Dangerfield

Answer 9

Improved query is:

SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL < (SELECT Min(a) FROM test1)
 MINUS
SELECT a FROM test1;

Note: a is column in which we find missing value.

Qualified

Answer 10

Try with a subquery:

SELECT A.EMPNO + 1 AS MissingEmpNo
FROM tblEmpMaster AS A
WHERE A.EMPNO + 1 NOT IN (SELECT EMPNO FROM tblEmpMaster)

Teevens

Answer 11

 select    A.ID + 1 As ID
From [Missing] As A
Where A.ID + 1 Not IN (Select ID from [Missing])
And A.ID < n

Data: ID
1
2
5
7
Result: ID
3
4
6

Quart

Missing sequence numbers. – Quart

Please edit your post to provide context to your answer. Code-only answers are only partially helpful: stackoverflow.com/help/how-to-answer – Homogenize