Re: sql question

On Mon, 27 Sep 2004 12:04:06 -0500, "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.
>
>Thank you for your time.
>
>
>

Since you apparently are using integers only, do yourself and others the favor of defining the num column as an integer instead of a float

create table mynum(
num number(10) constraint mynum_pk primary key );

The following finds the first free/available slot in the sequence, if any :

select min(t)from
(select rownum as t,num from (select num from mynum order by num) where num > 0)
where t < num;

If no slot is free in between, it must be the the highest number + 1, thus the resulting SQL is:

select nvl(a.freeslot,b.newslot) from
(select min(t) as freeslot from
(select rownum as t,num from (select num from mynum order by num) where num > 0)
where t < num) a,
(select max(num) + 1 as newslot from mynum) b;

• Kenneth Koenraadt