Re: SQL to show largest gaps

On 1/19/2017 10:59 AM, Charles Wilt wrote:

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

Thanks for the reply Charles. I had already Gaagled it and found some of the same websites. But you're right - I should've include the term "DB2" to help narrow the scope.

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~