RE: SQL - Question

Try this.


For the lowest missing number:

select a.number+1
from numbers a
exception join numbers b on a.number+1=b.number
order by a.number+1
fetch first row only

The exception join gives you any number N where the file does not also
contain N+1.
The lowest of those numbers is one less than the lowest missing number.


For the highest missing number:

select a.number-1
from numbers a
exception join numbers b on a.number-1=b.number
order by a.number-1 desc
fetch first row only

This exception join gives you any number N where the file does not also
contain N-1.
The highest of those numbers is one more than the highest missing number.

That was fun. Hope it helps!
Richard

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jan
Grove Vejlstrup
Sent: Tuesday, December 01, 2015 4:33 AM
To: Midrange Systems Technical Discussion
Subject: SQL - Question

Hello,

I have a file with one field:

1
2
4
5
6
8
9

Is it possible with SQL to get the lowest number *not* in the file, here 3?
Or the highest, here 7.

Best regards

Jan

--