Re: SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/28
Message-ID: <34d03c2c.4365667@192.86.155.100>#1/1

On Wed, 28 Jan 1998 12:22:23 -0000, "Josef Huber" <josef.huber_at_ennstal.at> wrote:

>please help
>
>I have a table test with one column val.
>
>Test
>1

 [snip]
>13
>
>I need a query with the below output
>
>Output:
>2

 [snip]
>12
>

One way to accomplish the above is as follows:

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
/

      RNUM

---

         2
         3
         5
         6

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

>I need also a query where i can find the lowest val + 1 from the table
>Is this query a good way to do this ?
>

The lowest value in the table plus one would just be:

select min(val)+1 from t;

>SELECT val + 1 FROM test t
>WHERE val NOT IN
> (SELECT val FROM test
> WHERE val=t.val+1)
>
>
>
>PS: sorry for my english
>
>
>Ciao,
> Joe
>
>E-Mail: josef.huber_at_ennstal.at
>Home: http://www.liezen-online.at
>
>
 

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 Wed Jan 28 1998 - 00:00:00 CST