Re: sql quiz

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/06
Message-ID: <34b2a94f.4161691@www.sigov.si>#1/1

On Tue, 06 Jan 1998 11:39:56 -0500, Naren Chintala <naren_at_att.com> wrote:

>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?

If you have any table that contains at least as many rows as your table t1 than you can use this query (for your example the table SCOTT.EMP will do):

SELECT ROWNUM FROM scott.emp
  WHERE ROWNUM <= (SELECT MAX(c1) FROM t1) MINUS
SELECT c1 FROM t1;

>Thanks
>Naren

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jan 06 1998 - 00:00:00 CST