Re: SQL to show largest gaps

date: Thu, 19 Jan 2017 10:24:57 -0600
from: "T. Adair" <tadair@xxxxxxxxxxxxxxxx>
subject: SQL to show largest gaps

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~