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!
Good - The following is another possibability.
create table spam (col1 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
case when (0 < (select min(col1) from spam))
then 0
else min(a.col1)
end case
from spam a
where a.col1+1 not in (select col1 from spam);
Scott Black wrote:
> 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!
Opps - left out a rather critical piece...
Change min(a.col1) to min(a.col1 + 1) in the else clause...
Madison Pruet wrote:
> Good - The following is another possibability.
>
> create table spam (col1 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
> case when (0 < (select min(col1) from spam))
> then 0
> else min(a.col1)
> end case
> from spam a
> where a.col1+1 not in (select col1 from spam);
>
> Scott Black wrote:
>
> > 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!
Scott Black wrote: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);
this will cause a problem if there are not gaps, or if all of the gaps are
>=2 in size.
Try:
SELECT MIN(S1.a) + 1
FROM spam S1
WHERE NOT EXISTS ( SELECT 1
FROM spam S2
WHERE S2.a = S1.a + 1 );
This will cause hassles if there are lots of gaps. Or (7.[>3] and 9.[>2])
SELECT FIRST 1 S1.a + 1
FROM Spam S1
WHERE NOT EXISTS ( SELECT 1
FROM Spam S2
WHERE S2.a = S1.a + 1 )
ORDER BY 1;
The first avoids a join.
BTW: You **really** want to avoid doing this. It is expensive, and although
(being an anal retentive engineer) I understand the attraction of eliminating
all gaps, this rarely does anything for you.
Hope this helps!
KR
Pb
Scott Black wrote: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);
this will cause a problem if there are not gaps, or if all of the gaps are
>=2 in size.
Try:
SELECT MIN(S1.a) + 1
FROM spam S1
WHERE NOT EXISTS ( SELECT 1
FROM spam S2
WHERE S2.a = S1.a + 1 );
This will cause hassles if there are lots of gaps. Or (7.[>3] and 9.[>2])
SELECT FIRST 1 S1.a + 1
FROM Spam S1
WHERE NOT EXISTS ( SELECT 1
FROM Spam S2
WHERE S2.a = S1.a + 1 )
ORDER BY 1;
The first avoids a join.
BTW: You **really** want to avoid doing this. It is expensive, and although
(being an anal retentive engineer) I understand the attraction of eliminating
all gaps, this rarely does anything for you.
Hope this helps!
KR
Pb