Re:

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

David

Getting back to your question - I'd done a google on something that 
brought up solutions similar to some given here, with the problem of 
getting the number 1 when the sequence starts with something > 1.

Your solution of exception join against a table of acceptable values 
is a good one.

I ran another google on 'sql missing numbers' there are lots of hits. 
The first one mentions your exception join. It also has something 
like the solutions here, but seems also not able to catch the missing 
value if it is the first in a sequence. These boundary issues are killers!!

But google is a tremendous help in these kinds of questions - SQL is 
sufficiently universal and consistent, that we can stand on the 
shoulders of others to get many things solved.

Vern

At 03:57 AM 4/16/2008, you wrote:

> Hi all,
>
> Is there a way to select the numbers missing from a list of numbers ?
>
> I mean, if I have a table with keys  1, 2, 3, 5, 6, 9,
>
> can I find the numbers missing between 1 and 10? ie, return 4, 7, 8, 10.
>
> I could create a table with 1 zone filled with the numbers 1 to 10 
> and then do an outer join. But I need this in a program. I need to 
> find the smallest missing number in a given range of numbers.
>
> Thanks.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.