Re: SQL competition: the shortest interval coalesce/pack query

lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0412031228.3051dff_at_posting.google.com>...
> "Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:<2uPrd.61$G45.36_at_news.oracle.com>...

[...]
>
> I have not looked at the article, so I'm not sure this is any shorter but:
>
> with cover(x,y,n) as (
> select x, y, 1 from intervals
> union all
> select c.x, i.y, n+1 from cover c, intervals i
> where c.y > i.x
> and (n+1) < (select count(1) from intervals)
> )
> select x, max(y) from cover c
> where not exists (
> select 1 from cover where y=c.y and x < c.x
> ) group by x
>

Hmmm, on second thought. This explodes with just a few more intervals. I will have to come up with something slightly smarter.

/Lennart Received on Fri Dec 03 2004 - 20:21:45 CST