Missing Numbers (Between two numbers)

[Sathy]

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


---Sathy

[jmodic]

SELECT :v_lower + ROWNUM AS missing_number
FROM ALL_OBJECTS
WHERE ROWNUM < :v_upper - :v_lower;

[JMac]

If I understood that, Jurij, I might be impressed

[DaPi]

It depends on what you mean by a "missing number". Now, if the number " " went missing . . . . . . hey! where's " "? Has any one seen " "?

[Sathy]

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

[aspdba]

SQL> create table test as select rownum rno from user_objects where rownum<10;

Table created.

Elapsed: 00:00:00.47
SQL> select * From test;

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

9 rows selected.

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

1 row deleted.

Elapsed: 00:00:00.40
SQL> commit;

Commit complete.Elapsed: 00: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

[abhaysk]

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

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

[DaPi]

Do either of the above allow for a gap of two or more?

How about:

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!

[jmodic]

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):

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).

[jmodic]

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!