Re: sql question

"Karl Schendel" <schendel_at_kbcomputer.com> wrote in message news:schendel-7F486B.15321727092004_at_netnews.comcast.net...
| 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

add an nvl()

++ mcs Received on Mon Sep 27 2004 - 14:55:53 CDT