locked
how to find missing numbers in a sequence? RRS feed

  • Question

  • User-28043379 posted

    Hi,

    Can any one help me to find the missing numbers in a list of numbers by Oracle Sql Query?

    Here is the senario,

    following is a SQL Table with only one column,

    |  SNo |

       1

       2

       4

       5

       8

       9

      10

    In the above table i need to find a missing numbers from the 1 to 10 series, at prod machine the range could be 1 to 500000 or more than 5 Lak too.

    Finally i need a query, that should return the missing numbers as follows,

    |  Missing Numbers|

       3

       6

       7

    Can any one help me to find the missing numbers using the Oracle sql Query?

    Monday, January 7, 2013 3:15 AM

Answers

  • User1428336426 posted

    SELECT distinct number
    FROM master..spt_values
    WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)
    AND number NOT IN (SELECT id FROM MyTable)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2013 4:46 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2013 4:54 AM
  • User151468730 posted

    Hello there,

    With regards to your question, please refer to the following:

    http://www.dbforums.com/oracle/1655570-oracle-function-sql-find-sequential-numbers-skipped.html

    It contains information and questions similar if not identical to you own.

    Hope this helps!

    Best of Luck!

    With Kind Regards,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2013 11:09 PM

All replies

  • User1428336426 posted

    SELECT distinct number
    FROM master..spt_values
    WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)
    AND number NOT IN (SELECT id FROM MyTable)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2013 4:46 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2013 4:54 AM
  • User151468730 posted

    Hello there,

    With regards to your question, please refer to the following:

    http://www.dbforums.com/oracle/1655570-oracle-function-sql-find-sequential-numbers-skipped.html

    It contains information and questions similar if not identical to you own.

    Hope this helps!

    Best of Luck!

    With Kind Regards,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2013 11:09 PM