Re: Query to find contiguous ranges on a column

Peter Hunsberger <peter.hunsberger@gmail.com> wrote:

> [...]
>> or a recursive query (which I always find very hard to com-
>> prehend):

>> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS
>> |   (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
>> |    UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE c.col = p.Value + 1)
>> |   SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
>> |     GROUP BY LeftBoundary
>> |     ORDER BY LeftBoundary;

>> Could you run both against your data set and find out which
>> one is faster for your six million rows?

> Turns out the server is v 8.3, looks like I need to get them to
> upgrade it so I get recursive and windowing :-(.  If this happens any
> time soon I'll let you know the results.

> Many thanks.

After some tests with a data set of 7983 rows (and 1638 ran-
ges): Don't! :-) The recursive solution seems to be more
than double as slow as the iterative. I'll take it to -per-
formance.

Tim