Missing Numbers (Between two numbers)

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