AW: SQL - Question

Yep! It can be done with a recursion:

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

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
Brown)

"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



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



--

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: <http://lists.midrange.com/mailman/listinfo/midrange-l>
http://lists.midrange.com/mailman/listinfo/midrange-l

or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to
review the archives at <http://archive.midrange.com/midrange-l>
http://archive.midrange.com/midrange-l.