Re: SQL to show largest gaps

[Mike Jones <mike.jones.sysdev@xxxxxxxxx>]

Hi TA,

select A.TICKET#, B.NEXT_TICKET#, B.GAP
from FILEA  A
cross join lateral (
  select B.TICKET# as NEXT_TICKET#, B.TICKET# - A.TICKET# as GAP
  from FILEB  B
  where B.TICKET# > A.TICKET#
  order by B.TICKET#
  fetch first row only
) as B
order by B.GAP desc

Regards,

Mike


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