1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How To Find Missing Number in a Sequence?

Discussion in 'SQL PL/SQL' started by jagadekara, Aug 10, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    How To Find Missing Number?

    Code (SQL):

    ---------------------------------------------------------------------------------
    CREATE TABLE test2 (sno NUMBER);
    ---------------------------------------------------------------------------------
    INSERT INTO test2 VALUES(1);
    INSERT INTO test2 VALUES(2);
    INSERT INTO test2 VALUES(3);
    INSERT INTO test2 VALUES(6);
    INSERT INTO test2 VALUES(8);
    ---------------------------------------------------------------------------------

    SELECT * FROM test2;

    SNO
    ----
    1
    2
    3
    6
    8

    ---------------------------------------------------------------------------------

    SELECT min_sno - 1 + level missing_number
    FROM ( SELECT MIN(sno) min_sno
    , MAX(sno) max_sno
     FROM test2
      )
     CONNECT BY level <= max_sno - min_sno + 1
      minus
     SELECT sno
     FROM test2
    ---------------------------------------------------------------------------------

    MISSING_NUMBER
    ---------------
    4
    5
    7
     
     
  2. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    can anyone explain me the execution flow of this query

    --------------------------------------------------------------------------------
    CREATE TABLE test2 (sno NUMBER);
    ---------------------------------------------------------------------------------
    INSERT INTO test2 VALUES(1);
    INSERT INTO test2 VALUES(2);
    INSERT INTO test2 VALUES(3);
    INSERT INTO test2 VALUES(6);
    INSERT INTO test2 VALUES(8);
    ---------------------------------------------------------------------------------

    SELECT * FROM test2;

    SNO
    ----
    1
    2
    3
    6
    8

    ---------------------------------------------------------------------------------

    SELECT min_sno - 1 + level missing_number
    FROM ( SELECT MIN(sno) min_sno
    , MAX(sno) max_sno
    FROM test2
    )
    CONNECT BY level <= max_sno - min_sno + 1
    minus
    SELECT sno
    FROM test2
    ---------------------------------------------------------------------------------

    MISSING_NUMBER
    ---------------
    4
    5
    7
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Create
    Insert
    Select