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