Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!not-for-mail
From: lennart@kommunicera.umea.se (Lennart Jonsson)
Newsgroups: comp.databases.theory
Subject: Re: SQL competition: the shortest interval coalesce/pack query
Date: 3 Dec 2004 18:21:45 -0800
Organization: http://groups.google.com
Lines: 25
Message-ID: <6dae7e65.0412031821.4df452c6@posting.google.com>
References: <2uPrd.61$G45.36@news.oracle.com> <6dae7e65.0412031228.3051dff@posting.google.com>
NNTP-Posting-Host: 194.236.201.101
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1102126905 1672 127.0.0.1 (4 Dec 2004 02:21:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 4 Dec 2004 02:21:45 +0000 (UTC)
Xref: dp-news.maxwell.syr.edu comp.databases.theory:28832

lennart@kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0412031228.3051dff@posting.google.com>...
> "Mikito Harakiri" <mikharakiri@iahu.com> wrote in message news:<2uPrd.61$G45.36@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