Posted by David Portas on 10/04/07 20:31

"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...
>

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

--
David Portas

[Back to original message]