>>Anybody got any spiffy tricks to find the gaps in a range using SQL?
>>
>>Say I've got a table with:
>>
>>

>>Start   End
>>    1     5
>>    6     8
>>   12    15
>>   19    24
>>

>>
>>I'd like to find the gaps (9-11,16-18). One other thing, I'd also like to be able to give it some limits like "find the gaps between 10-17" so it should return 10-11, 16-17, in that case. Or do I have to do this by just brute force, since I need to look at 2 records at a time?
>
>Fred,
>
>How about,
>

>    select x.End+1, MIN(y.start)-1 as xstart ;
>           from table x, table y ;
>           where x.end < y.start ;
>           group by x.end ;
>           having xstart - x.end >= 1
>

>As for the limits: add an and x.end > 10 and x.start < 17 to the where clause.
>
>Walter,

Walter,

Your solution comes close, but doesn't quite work, but I think I know why. In the above sample data it works perfectly. But with this sample set, it fails by indicating gaps where there exists data within the range:

Data Set:         Result:             Expected:
Start  End
25200  43200      43201  43439        43201 43439
43440  44640      44641  44819 ??     44861 44879
44640  44820      44821  44879 ??     44926 44939
44820  44860      44861  44879
44880  44925      44926  44939
44940  44990

I failed to mention that the end of one set might be the same as the start of the next. I think that's why the difference in the results from what was expected. I'll work with what you posted, I may be able to sort it out.
Thanks for the excellent head start!