Posted by David Portas on 10/04/07 21:46

"Zamdrist" <zamdrist@gmail.com> wrote in message
news:1191525448.343516.292440@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

[Back to original message]