Re:

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.