Re: SQL to show largest gaps

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Nice thing about SQL....it's used everywhere...you're not limited to
Midrange related sites.

http://lmgtfy.com/?q=finding+gaps+in+sequence+sql

You might want to include DB2 in the search terms to to limit the
appearance of solutions that use MS/Oracle/ect specific functions.

However, as you'll see from the search, it's well known problem "Gaps &
Islands".

You might find some of the non-DB2 answers informative.

Charles

On Thu, Jan 19, 2017 at 9:24 AM, T. Adair <tadair@xxxxxxxxxxxxxxxx> wrote:

> I have a physical file with a ticket# field (5,0).  It should be mostly in
> sequence with small gaps.  Unfortunately there are some large gaps and I
> need to track those down.  I am an SQL novice but I'm trying to use SQL to
> show the gaps.  As in...
>
> Tkt#    Next Tkt#    Gap
>   1         2         1
>   2         3         1
>   3         7         4
>   7         8         1
>   8        13         5
>  13        15         2
>  15       335        220
> 335       336         1
> Etc.
>
> But, I would like it to show this in sequence by gap size (desc), as in...
>
> Tkt#    Next Tkt#    Gap
>  15       335        220
>   8        13         5
>   3         7         4
>  13        15         2
>   1         2         1
>   2         3         1
> Etc.
>
> Here's my latest attempt (please don't laugh at my mess).
>
> select a.ticket#,
>        (select ticket# from fileb b
>         where b.ticket# > a.ticket#
>         fetch first 1 row only ),
>        b.ticket# - a.ticket# as gap
> from filea a
> order by gap desc
>
>
> It tells me, "Column or global variable TICKET# not found."
>
> I've searched the archives but didn't find anything like this.  Am I even
> on the right track?
>
> Thoughts?  Advice?  Suggestions?
>
> Thanks in advance.
>
> ~TA~
>
> --
> 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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD