Re: SQL to show largest gaps

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~