Re: SQL for intervals

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 12 Dec 2006 14:21:50 GMT
Message-ID: <2Wyfh.31895$cz.476399_at_ursa-nb00s0.nbnet.nb.ca>

Tonkuma wrote:

> Bob Badour wrote:
> 

>>select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
>>from (
>> select id as id_lwr, t as min_t from W W1 where not exists (
>> select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
>> )
>>) WL,
>>(
>> select id as id_upr, t as max_t from W W3 where not exists (
>> select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
>> )
>>) WU
>>where id_lwr = id_upr and max_t > min_t
>>group by id_lwr, min_t
>>;
>>
>>Depending on your flavour of SQL, you might have to change the simple
>>additions to function calls like DATE_ADD etc.

> 
> Same kind of thinking. But it is not necessary to use nested table
> expression.
> SELECT lbound.id
>      , CHAR(lbound.t,USA)      "LowerBound"
>      , CHAR(MIN(ubound.t),USA) "UpperBound"
>   FROM W lbound
>      , W ubound
>  WHERE NOT EXISTS
>        (SELECT *
>           FROM W lower
>          WHERE lower.id = lbound.id
>            AND lower.t  = lbound.t - 1 day
>        )
>    AND ubound.id = lbound.id
>    AND ubound.t >= lbound.t
>    AND NOT EXISTS
>        (SELECT *
>           FROM W upper
>          WHERE upper.id = lbound.id
>            AND upper.t  = ubound.t + 1 day
>        )
>  GROUP BY
>        lbound.id, lbound.t

How is a table expression nested in the where clause any less nested than one in the from clause? Received on Tue Dec 12 2006 - 15:21:50 CET