Re:

[Michael_Schutte@xxxxxxxxxxxx]

the way you wrote it will not work....  because of the and... a.thekey
cannot equal both  b.thekey and b.thekey + 1.     I said field not key.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.





midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:31:05 AM:

> I didn't understand this.  So I tried
>
> Select  Min(thekey) FROM qtemp.foxwell a
>  WHERE not exists (select '1' from qtemp.foxwell b
> where a.thekey = b.thekey
>  and  a.thekey = b.thekey + 1)
>
> on this table
> CREATE TABLE QTEMP.FOXWELL (THEKEY INT NOT NULL WITH DEFAULT)
> with these values:  2, 3, 5
> and I got a 2 as a result.
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Michael_Schutte@xxxxxxxxxxxx
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 04/16/2008 08:20 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Re:
>
>
>
>
>
>
> sorry I put the +1 on the wrong field.
>
>
> Michael Schutte
> Admin Professional
>
>
>
>
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:18:25 AM:
>
> > Exec SQL
> > Select  Min(Field) FROM FILE  a
> >  WHERE not exists (select '1' from file b where a.key1 = b.key1 and
> a.field
> > = b.field+1)
> >
> >
> > Michael Schutte
> > Admin Professional
> > Bob Evans Farms, Inc.
> >
> >
> >
> >
> >
> > midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 04:57:07 AM:
> >
> > > 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.
> >
> > --
> > 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.
>
> --
> 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.
>
>
> --
> 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.