RE: SQL to show largest gaps

Yet an other version using the OLAP function ROW_NUMBER OVER() (available
since Release V5R4M0) in a self-join

With x as (Select Row_Number() Over(Order By Ticket) counter, a.*
from MyGap a)
Select x.Ticket, y.Ticket NextTicket,
y.Ticket - x.Ticket Gap
from x join x y on x.Counter = y.Counter - 1
Order By Gap Desc

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of T.
Adair
Sent: Donnerstag, 19. Januar 2017 18:45
To: midrange-l@xxxxxxxxxxxx
Subject: 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~

--
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