Re: Box query

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 18:52:05 GMT
Message-ID: <pXu2g.64297$VV4.1208215@ursa-nb00s0.nbnet.nb.ca>

J M Davitt wrote:

> Bob Badour wrote:
>

>> J M Davitt wrote:
>>
>>> [snip]
>>> I believe this gives volumes:
>>
>>
>> I don't know whether it does, but it seems rather complex compared to 
>> using exp, ln and sum as Mikito's solution does. His solution makes 
>> the volume easier to express because he orders box1 and box2 by low 
>> instead of by id. That would seem to make my query somewhat less 
>> excellent vis a vis his.

>
>
> Yes, it is tall. The whole business turning columns in rows from
> self-correlated queries into rows of columns is messy. (And hard to
> describe, too!) And, yes, it seems complex -- lots of JOINs.
>
> Regarding Mikito's solution: do you mean this?
>
> select b1.id as box1, b2.id as box2
> from boxes b1, boxes b2
> where b1.dim = b2.dim
> group by b1.id, b2.id
> having 0.1 < exp(sum(ln(
> case when b2.low between b1.low and b1.high then b1.high-b2.low
> else 0.00000001 end
> )))
>
> Two things: I don't see that this gives volume and I can't see how
> he avoids "ln(0)" when b1.high = b2.low -- when it might be if
> 'b2.low between b1.low and b1.high' is true.
>
> Or did I miss something in the discussion?

I found the same flaws in his solution when I examined it closer. I replied to the message with what I think is a correct solution. Received on Sat Apr 22 2006 - 13:52:05 CDT