Re: SQL to show largest gaps

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