Let's say I have a result set (records) containing numbers such as:

0
1
2
3
9
16
21
45
100
101
102
103
104
105
106

How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.

Thoughts? Thanks...

"Zamdrist" <zamdrist [at] gmail.com> wrote in message
news:1191525448.343516.292440 [at] k79g2000hse.googlegroups.com.. .
> Let's say I have a result set (records) containing numbers such as:
>
> 0
> 1
> 2
> 3
> 9
> 16
> 21
> 45
> 100
> 101
> 102
> 103
> 104
> 105
> 106
>
> How might someone write a procedure to get the next lowest number in
> this sequence? In this case it would be: 4. Some combination of
> Select, Min & When, I am sure, but it escapes me at the moment.
>
> Thoughts? Thanks...
>

SELECT MIN(x)+1 x
FROM tbl t
WHERE NOT EXISTS
(SELECT x
FROM tbl
WHERE x = t.x+1);

--
David Portas

On Oct 4, 3:31 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor... [at] acm.org> wrote:
> "Zamdrist" <zamdr... [at] gmail.com> wrote in message
>
> news:1191525448.343516.292440 [at] k79g2000hse.googlegroups.com.. .
>
>
>
> > Let's say I have a result set (records) containing numbers such as:
>
> > 0
> > 1
> > 2
> > 3
> > 9
> > 16
> > 21
> > 45
> > 100
> > 101
> > 102
> > 103
> > 104
> > 105
> > 106
>
> > How might someone write a procedure to get the next lowest number in
> > this sequence? In this case it would be: 4. Some combination of
> > Select, Min & When, I am sure, but it escapes me at the moment.
>
> > Thoughts? Thanks...
>
> SELECT MIN(x)+1 x
> FROM tbl t
> WHERE NOT EXISTS
> (SELECT x
> FROM tbl
> WHERE x = t.x+1);
>
> --
> David Portas

Thanks!

"Zamdrist" <zamdrist [at] gmail.com> wrote in message
news:1191525448.343516.292440 [at] k79g2000hse.googlegroups.com.. .
> Let's say I have a result set (records) containing numbers such as:
>
> 0
> 1
> 2
> 3
> 9
> 16
> 21
> 45
> 100
> 101
> 102
> 103
> 104
> 105
> 106
>
> How might someone write a procedure to get the next lowest number in
> this sequence? In this case it would be: 4. Some combination of
> Select, Min & When, I am sure, but it escapes me at the moment.
>
> Thoughts? Thanks...
>

Another method (SQL Server 2005 only):

SELECT MIN(x) x
FROM
(SELECT x+1 FROM tbl
EXCEPT
SELECT x FROM tbl) t(x);

--
David Portas

Zamdrist (zamdrist [at] gmail.com) writes:
> Let's say I have a result set (records) containing numbers such as:
>
> 0
> 1
> 2
> 3
> 9
> 16
> 21
> 45
> 100
> 101
> 102
> 103
> 104
> 105
> 106
>
> How might someone write a procedure to get the next lowest number in
> this sequence? In this case it would be: 4. Some combination of
> Select, Min & When, I am sure, but it escapes me at the moment.

Here's another that requires SQL 2005:

SELECT MIN(rn - 1 + minacsid)
FROM (SELECT acsid, rn = row_number() Over(ORDER BY acsid),
minacsid = MIN(acsid) OVER()
FROM accountstats) AS x
WHERE rn - 1 + minacsid <> acsid



--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.

>> How might someone write a procedure to get the next lowest number in this sequence? In this case it would be: 4. <<

No; read your own specs! The answer is -1. Or maybe -0.0000...1 if
the column is a DECIMAL or a FLOAT. If you had posted DDL that
limited the column to non-negative integers, then Dave's answer would
work.

On Oct 4, 2:17 pm, Zamdrist <zamdr... [at] gmail.com> wrote:
> In this case it would be: 4.

SELECT MIN(t2.x +1)
FROM t FULL JOIN t t2 ON t.x = t2.x +1
WHERE t.x IS NULL;

On 6 Oct, 18:34, Rodrigo De Le=F3n <rdele... [at] gmail.com> wrote:
> On Oct 4, 2:17 pm, Zamdrist <zamdr... [at] gmail.com> wrote:
>
> > In this case it would be: 4.
>
> SELECT MIN(t2.x +1)
> FROM t FULL JOIN t t2 ON t.x =3D t2.x +1
> WHERE t.x IS NULL;

How about:

SELECT t1.sequence_id +1 vacancy
FROM sequence t1
LEFT JOIN sequence t2 ON t1.sequence_id +1 =3D t2.sequence_id
WHERE t2.sequence_id IS NULL
ORDER BY vacancy
LIMIT 1