Naren Chintala (naren@att.com) wrote:
: Hi,
: SQL> describe t1;
:  Name                            Null?    Type
:  ------------------------------- -------- ----
:  C1                                       NUMBER
: 
: SQL> select c1 from t1;
: 
:         C1
: ----------
:          1
:          4
:          7
:          8
:          9
:         10
: 
: Question:
: 
: I need to retrieve the missing numbers (2,3,5,6) from t1.
: How can I do this in ONE sql statement? 
: I know that this can be done in PL/SQL.
: 
: Any ideas? 
: Thanks
: Naren
Naren,

The following will work:

  1  select '1' from dual union
  select '2' from dual union 
 select '3' from dual union
 select '4' from dual union
 select '5' from dual union
 select '6' from dual union
 select '7' from dual union
 select '8' from dual union
 select '9' from dual union
 select '10' from dual
 minus select * from t1
/

The result will be 2,3,5,6.

-Ari Kaplan
Independent Oracle DBA Consultant
________________________________________________
Vinay Dheer  writes:

I was looking at the above tip, when an alternate solution came to mind.

select rownum
from    user_objects /* (or some such large table) */
where rownum < (select max(c1) from t1)
minus select * from t1

The advantage is that you will not have to know the largest number in t1 and the obvious disadvantage is that you must have a table with that many rows.

Thanks for maintaining the tips page. It came handy today.

Regards,

Vinay Dheer

Back to Ari Kaplan's Home Page ">Back to Ari Kaplan's Home Page