Re: sql question

On Mon, 27 Sep 2004 17:36:12 GMT, Kenneth Koenraadt wrote:

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

2 more things :

a)
The above statement does not handle an empty table or one with all numbers < 0. Here's release 2.0. which does :

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 nvl(max(num),0) + 1 as newslot from mynum where num > 0) b;

b)
Do not expect any concurrency or scalability capabilities from your application. Either you must lock the table exclusively or be prepared to handle frequent primary key violations.

• Kenneth Koenraadt