Re:

[Michael_Schutte@xxxxxxxxxxxx]

See, I knew if I got the ball rolling someone would come up with the right
statement.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.



midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 09:20:11 AM:

> David FOXWELL 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.
>
> Hi David!
>
> The easiest way to do this is to look at every key in the file and find
> one where the next key doesn't exist.  To do that, create a temporary
> table with a list of next keys (potential holes).  If your lowest key
> may not be in the file, you have to include that manually in your list
> of potential keys.
>
> So just break it down by task:
>
>  // Create list of potential holes as every key in the file +1
>  With T1 as
>    // Include 1 by using the system dummy file
>    (select 1 as potential from sysibm/sysdummy1 union all
>     select keyfield+1 as potential from keys)
>  // Now select the smallest one without a match
>  select min(potential) from t1 where not exists
>    (select 1 from keys where potential = keyfield)
>
>
> Joe
> --
> 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.