Retrieve next lowest number in mixed set

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@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

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...@.acm.orgwrote:

Quote:

Originally Posted by

"Zamdrist" <zamdr...@.gmail.comwrote in message
>
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...
>
>
>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

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@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Quote:

Originally Posted by

>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...@.gmail.comwrote:

Quote:

Originally Posted by

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;