Re: sql quiz

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/09
Message-ID: <34ba995e.40308360@inet16>#1/1

Here is a slighty more generic way to do it...

drop table foo;

create table foo ( x int );

insert into foo values ( 1 );
insert into foo values ( 4 );
insert into foo values ( 7 );
insert into foo values ( 8 );
insert into foo values ( 9 );
insert into foo values ( 10 );

commit;

select rnum
  from ( select rownum rnum

           from all_objects
          where rownum <= ( select max(x) from foo )
       ), foo

 where rnum = x (+)
   and x is null
/

We use the inline view "select rownu rnum ... " to generate a set of numbers 1..max(x). We then outer join the original set of numbers in foo to this set, only keeping the rows from this join such that foo didn't have a value.

that way, if the number of rows/max value in foo changed, the query would still work. You just need to make sure that the table you use in the inline view (all_objects in the above example) has at least as many rows in it as the max value of X......

On 7 Jan 1998 17:40:55 GMT, zhouf_at_rintintin.Colorado.EDU (Fengqing Zhou) wrote:

>In article <34B25E5C.235C_at_att.com>, Naren Chintala <naren_at_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
>
>
>how about
>select 1 from dual union all
>select 2 from dual union all
>select 3 from dual union all
>select 4 from dual union all
>select 5 from dual union all
>select 6 from dual union all
>select 7 from dual union all
>select 8 from dual union all
>select 9 from dual union all
>select 10 from dual
>minus
>select c1 from t1
>/
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 09 1998 - 00:00:00 CST