Yep! It can be done with a recursion:

1. Build a counter (may be starting and end positions need to be

2. Join the counter with an exception join with your table

3. Select the minimum.

Something like this:

With Counter (Nbr)

as ( Values(1)

Union All

Select Level + 1 from Counter

Where Nbr < 1000

Select Min(Nbr)

from Counter exception join YourTable on Level = YourNumber

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les

"If you think education is expensive, try ignorance." (Derek Bok)

"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jan
Grove Vejlstrup
Gesendet: Tuesday, 01.12 2015 10:33
An: Midrange Systems Technical Discussion
Betreff: SQL - Question


I have a file with one field:








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

Best regards



This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxx>
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,

visit: <>

or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to
review the archives at <>

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].