Re: SQL Question - how to find next available number

And one final opps... I forgot about the situation where we want to increase the range, that is 0-5 are all in use. Soooo - let's see just how convoluted I can get and still utilize only a single query.... ;-) select case -- gaps at start case - return 0 when (0 < (select min(col1) from spam)) then 0 -- no gaps - return next number when ( 0 == (select count(*) from spam where col1 + 1 not in (select col1 from spam))) then (select max(col1) + 1 from spam) -- gaps - return lowest gap number else min(a.col1+1) end case from spam a where a.col1+1 not in (select col1 from spam); Madison Pruet wrote: > 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!