RE: SQL Question - how to find next available number
For some reason this didn't make it to nospam@mail.org :). But here is a
much more accurate version:
drop table spam;
create temp table spam(field1 int);
insert into spam values(1);
insert into spam values(2);
insert into spam values(2);
insert into spam values(3);
insert into spam values(4);
insert into spam values(5);
insert into spam values(5);
insert into spam values(5);
insert into spam values(6);
insert into spam values(7);
insert into spam values(8);
insert into spam values(9);
insert into spam values(9);
insert into spam values(19);
insert into spam values(18);
select min(a.field1 + 1)
from spam a, spam b
where b.field1 > a.field1
and b.field1 - a.field1 = (select min(b.field1 - a.field1)
from spam a, spam b
where b.field1 - a.field1 > 1
and b.field1 > a.field1
and b.field1 -1 not in (select field1
from spam))
--= 2
and b.field1 - 1 not in (select field1 from spam);
-----Original Message-----
From: Scott Black
Sent: Thursday, October 05, 2000 4:23 PM
To: 'Zanrac'; ''Informix-List (E-mail)'
Subject: RE: SQL Question - how to find next available number
This seemed like a good challenge. Here's what I came up with quickly.
There should be a way to refine it and speed it up. I'm sure you can do
better with a little imagination, but this might be a place to start.
--drop table spam;
create temp table spam(field1 int);
insert into spam values(1);
insert into spam values(2);
insert into spam values(2);
insert into spam values(4);
insert into spam values(5);
insert into spam values(5);
insert into spam values(5);
insert into spam values(6);
select unique a.field1 + 1
from spam a, spam b
where b.field1 > a.field1
and b.field1 - a.field1 = 2
and b.field1 - 1 not in (select field1 from spam);
-----Original Message-----
From: Zanrac [mailto:nospam@mail.org]
Sent: Thursday, October 05, 2000 2:22 PM
Posted To: informix
Conversation: SQL Question - how to find next available number
Subject: SQL Question - how to find next available number
I would like to find the first available number in a table. Is it possible
and what would the SQL statement look like?
For exemple, field1 of table1 contains the following values:
1
2
2
4
5
5
5
6
I'd like my SQL statement to return 3. Thanx in advance!