Re: Box query

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 22 Apr 2006 18:35:58 GMT
Message-ID: <iIu2g.11871$P2.5057@tornado.ohiordc.rr.com>

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? Received on Sat Apr 22 2006 - 13:35:58 CDT