|
|
Posted by Erland Sommarskog on 10/04/07 22:02
Zamdrist (zamdrist@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@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|