Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: Get rows not present in table

    Hi All,

    Can you please help me to achieve below scenario.

    I have a table random_numbers, where I have inserted numbers between 1-10 and 80 - 90.

    Now, how can I get numbers 11-79 and 91-100 i.e. the numbers which are not present in the table without joining with other tables.

    Note: Above values is just an example, i.e. the query or for loop should not be hard coded.

    Your help is very much required.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Row generator" is the keyword here.

    An example: get numbers that aren't stored in the TEST table:
    Code:
    SQL> select * from test;
    
           COL
    ----------
             1
             3
             6
             7
            10
    
    SQL> select level
      2  from dual
      3  connect by level <= (select max(col) from test)
      4  minus
      5  select col from test;
    
         LEVEL
    ----------
             2
             4
             5
             8
             9
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •