Missing Numbers (Between two numbers)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Missing Numbers (Between two numbers)

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    72

    Missing Numbers (Between two numbers)

    Is there any option to get Missing numbers between
    two numbers with out using loop statement????


    ---Sathy

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT :v_lower + ROWNUM AS missing_number
    FROM ALL_OBJECTS
    WHERE ROWNUM < :v_upper - :v_lower;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    If I understood that, Jurij, I might be impressed

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It depends on what you mean by a "missing number". Now, if the number " " went missing . . . . . . hey! where's " "? Has any one seen " "?

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Actually Iam having table coloumn having values from 400000 to 500000.
    And some numbers are missing between these number..

    Is there any inbuild function is there to find out
    the missed numbers?.

    --Sathy

  6. #6
    Join Date
    Aug 2002
    Posts
    115
    PHP Code:
    SQLcreate table test as select rownum rno from user_objects where rownum<10;

    Table created.

    Elapsed00:00:00.47
    SQL
    select From test;

           
    RNO                                                                                                                    
    ==========                                                                                                                    
             
    1                                                                                                                    
             2                                                                                                                    
             3                                                                                                                    
             4                                                                                                                    
             5                                                                                                                    
             6                                                                                                                    
             7                                                                                                                    
             8                                                                                                                    
             9                                                                                                                    

    9 rows selected
    .

    Elapsed00:00:00.78
    SQL
    delete from test where rno=6;

    1 row deleted.

    Elapsed00:00:00.40
    SQL
    commit;

    Commit complete.Elapsed00:00:00.25
    SQL
    >  select rno+1 from test a
      2   where not exists 
    (select null from test b
      3   where b
    .rno=a.rno+1)
      
    4  /

         
    RNO+1                                                                                                                    
    ==========                                                                                                                    
             
    6                                                                                                                    
            10                                                                                                                    

    Elapsed
    00:00:00.19 


    http://www.experts-exchange.com/Data..._20815063.html

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    SELECT :v_lower + ROWNUM AS missing_number
    FROM ALL_OBJECTS
    WHERE ROWNUM < :v_upper - :v_lower;
    Jurij :

    I dont understand if this will fit what poster has asked for ..

    How about

    Code:
    Select Column_Name + 1 From My_Table Where Column_Name + 1 <= My_Max_Value And Column_Name + 1 >= My_Min_Value
    Minus
    Select Column_Name From My_Table Where Column_Name <= My_Max_Value And Column_Name >= My_Min_Value
    Last edited by abhaysk; 04-08-2004 at 08:41 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Do either of the above allow for a gap of two or more?

    How about:
    Code:
    Select * From 
    (SELECT :v_lower + ROWNUM AS missing_number
     FROM ALL_OBJECTS, ALL_OBJECTS
     WHERE ROWNUM < :v_upper - :v_lower)
    Where missing_number not in 
    (select my_col from my_table
     where  my_col is not NULL);
    With aknowledgements to Jurij.

    P.S: just noticed we need 100'000 numbers! Hope this won't blow the temp area!
    Last edited by DaPi; 04-08-2004 at 09:19 AM.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Jurij :

    I dont understand if this will fit what poster has asked for ..
    Of course it will not. That's because at the time when I wrote my ansewer there were far less information available about what Sathy realy wants than it was when you replied. I have missinterpreted the original question as "given the minimum and maximum nuber, can I get all the numbers between them without using loop statement". Now after Sathy's further explanation it is obvious that I have totaly misunderstood the question.

    However it is also obvious that your suggesten was equaly useless and wrong.

    Suppose we have three values in the table: 10, 13 and 17. Sowe want to get all the missing numbers, that is 11, 12, 14, 15 and 16. BTW, your proposed sollution returns only values 11 and 14.

    Here is my next porposal (with the table and column names you have used in your suggestion):
    Code:
    SELECT :my_min_value+rownum FROM all_objects
     WHERE rownum < :my_max_value - :my_min_value
    MINUS
    SELECT column_name FROM my_table;
    For any given two integers (my_max_value and my_min_value) it will list all the integers inside this range that are not allready in my table. The assumption is of course that the given range is not larger than number of rows returned by ALL_OBJECTS (typicaly more than 20,000 if you have java loaded into your database).
    Last edited by jmodic; 04-08-2004 at 09:27 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hm, just realised that DaPi allready posted the sollution that goes almost the same as mine. Plus I didn't noticed that in the example given by Sathy the requested range is 100,000! Gosh! Although performing a cartesian product between two resultsets from all_rows - hm, not every server will be able to cope with this!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width