Re: sql question

In article <hYqdnTbMX6C52sXcRVn-jQ_at_comcast.com>,  "Oxnard" <shankeyp_at_no-spam.comcast.net> wrote:

> I'm trying to generate an SQL that will return the lowest usable value which
> is higher than zero. Here is the situatuion:
>
> DB versio 9.2.0.5.
>
> create table mynum(
> num number constraint mynum_pk primary key,
> );
>
> insert into mynum(num) values (1);
> insert into mynum(num) values (2);
> insert into mynum(num) values (4);
> insert into mynum(num) values (5);
> insert into mynum(num) values (6);
>
> In this case I am looking for a 3 to be returned. I know I can do it with
> PL/SQL but I would like to use a single SQL statement.

select min(num)+1
from mynum
where num+1 not in (select num from mynum)

would seem to be the simplest solution, if you can assume that there's at least one row in the table.

Karl Received on Mon Sep 27 2004 - 14:32:17 CDT