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 (DOT) com> wrote

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 (DOT) org> wrote:
Thanks!

"Zamdrist" <zamdrist (AT) gmail (DOT) com> wrote

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 (DOT) com) writes:
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 (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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.

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 6 Oct, 18:34, Rodrigo De León <rdele... (AT) gmail (DOT) com> wrote:
How about:

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