Re: SQL to show largest gaps

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

Wouldn't you want to make sure you are processing fileb in ticket order? 
 (Note: not tested.)

with junk (x, y) as  (
select a.ticket#, (select ticket# from fileb b
                      where b.ticket# > a.ticket#
       /* >>>>>> */   ORDER BY B.TICKET#  /* <<<<<< */
                      fetch first 1 row only )
from filea a
)
select x,y, (y-x) as gap
from junk
order by gap desc

Sam


On 1/19/2017 11:44 AM, T. Adair wrote:

> After stumbling and bumbling around a bit, I finally worked out the
> following solution...:
>
> with junk (x, y) as  (
> select a.ticket#, (select ticket# from fileb b
>                       where b.ticket# > a.ticket#
>                       fetch first 1 row only )
> from filea a
> )
> select x,y, (y-x) as gap
> from junk
> order by gap desc
>
> It's QCD but it works.
>
> Thanks.
>
> ~TA~