Hi,
You may try something like this:
With x as (Select a.MySeq aSeq
From MyTable a exception join mytable b on a.MySeq =
b.MySeq+1),
Y as (select aSeq, MySeq bseq
From x join MyTable b on aSeq < mySeq)
Select aSeq, Min(bSeq)
From y
Group By aSeq
Assumed your counter is 1.
In the first CTE all Sequences are returned where no following sequence
(i.e. MySeq+1) can be found
In the second CTE the first CTE is joined with the original table but all
sequences that are larger than the found sequence is are returned.
In the final select the next larger sequence is returned.
Assumed my table looks like this:
MySeq
10
20
30
50
60
100
110
130
And my counter is 10
The result will be:
aSeq Min(bSeq)
30 50
60 100
110 130
That means 40, 70, 80, 90 and 120 are missing
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!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von James H. H. Lampert
Gesendet: Wednesday, 24. June 2009 17:27
An: Midrange Systems Technical Discussion
Betreff: Looking for gaps in a key sequence
Given a key sequence in a file, really just a series of numbers, does
anybody know of an algorithm, other than brute force, for finding gaps
in the sequence?
--
JHHL
midrange.com
