Re: Diffrence between rows in sql

["rick baird" <rick.baird@xxxxxxxxx>]

you're right.  It will only report the first hole in a contiguous series.

On 9/27/07, Alan Shore <AlanShore@xxxxxxxx> wrote:
> This problem intrigued me and BOTH are nice suggestions, but ( and I could
> be wrong on this) if the ones that are missing are
> 15
> 16
> 17
>
> 21
>
> this sql would NOT report 16 & 17 as missing
>
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
> "If you're going through Hell, keep going" - Winston Churchill
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 09/27/2007 10:59:18 AM:
>
> > Joe,
> >
> > great tip!  but an easier way to get the holes, change this:
> >
> > > SELECT A.SEQ FROM MYFILE A
> > >  WHERE NOT EXISTS
> > >    (SELECT 1 FROM MYFILE B WHERE B.SEQ = (A.SEQ+1))
> >
> > to:
> >
> > SELECT (A.SEQ+1) as holes FROM MYFILE A
> >  WHERE NOT EXISTS
> >    (SELECT 1 FROM MYFILE B WHERE B.SEQ = (A.SEQ+1))
> > --
> > 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.