Re: SQL help
From: Robert Stearns <is_at_uga.edu>
Date: Thu, 07 Oct 2004 10:22:03 -0400
Message-ID: <ck3je9$jom$1_at_cronkite.cc.uga.edu>
>
> [...]
>
>
>
> [...]
>
>
>
>
> [...]
>
> One way is to so something like:
>
> with suspects_1 (bhid, entity_id, starting, duration) as (
> select bhid, entity_id, starting, (ending - starting) seconds
> from owners
> where starting<=gd AND (ending IS NULL OR ending>=gd)
> ), suspects_2 (bhid, entity_id, starting, percent) as (
> select s1.bhid, s1.entity_id, s1.starting, o.percent
> from suspects_1 s1, owner o
> where (s1.bhid, s1.entity_id, s1.starting) = (o.bhid, o.entity_id, o.starting)
> and starting = (select MIN(starting) from suspects_1)
> ), suspects_3 (bhid, entity_id, starting) as (
> select bhid, entity_id, starting
> from suspects_2
> where percent = (select min(percent) from suspects_2)
> ...
>
>
>
> HTH
> /Lennart
Received on Thu Oct 07 2004 - 16:22:03 CEST
Date: Thu, 07 Oct 2004 10:22:03 -0400
Message-ID: <ck3je9$jom$1_at_cronkite.cc.uga.edu>
Thanks. I hadn't seen the WITH clause before (just how many pages of DB2 UDB documentation are there, any way), but looked it up in the SQL reference Vol. 1.
Lennart Jonsson wrote:
> Robert Stearns <rstearns1241_at_charter.net> wrote in message news:<10m6rtb9k95it9c@corp.supernews.com>...
>
>>This is cross posted because I honestly do not know which of the two >>news groups is more appropriate or more likely to yield a reply. >>
>
> [...]
>
>>The longest owner: MIN(starting) AND starting<=gd AND (ending IS NULL OR >>ending>=gd) >>If there ties, MAX(percent) among them >>If there are still ties, entity_id=geid if it exists >>else longest membership: MIN(entity_id) >>The last is guaranteed to be unique.
>
>
> [...]
>
>
>>CREATE TABLE owners >> ( >> bhid BIGINT NOT NULL, >> entity_id INTEGER NOT NULL, >> starting DATE NOT NULL, >> percent DECIMAL(7, 4) NOT NULL DEFAULT 100, >> ending DATE >> ) >> DATA CAPTURE NONE; >> >>ALTER TABLE owners >> ADD CONSTRAINT primary PRIMARY KEY >> (bhid, entity_id, starting);
>
>
> [...]
>
> One way is to so something like:
>
> with suspects_1 (bhid, entity_id, starting, duration) as (
> select bhid, entity_id, starting, (ending - starting) seconds
> from owners
> where starting<=gd AND (ending IS NULL OR ending>=gd)
> ), suspects_2 (bhid, entity_id, starting, percent) as (
> select s1.bhid, s1.entity_id, s1.starting, o.percent
> from suspects_1 s1, owner o
> where (s1.bhid, s1.entity_id, s1.starting) = (o.bhid, o.entity_id, o.starting)
> and starting = (select MIN(starting) from suspects_1)
> ), suspects_3 (bhid, entity_id, starting) as (
> select bhid, entity_id, starting
> from suspects_2
> where percent = (select min(percent) from suspects_2)
> ...
>
>
>
> HTH
> /Lennart
Received on Thu Oct 07 2004 - 16:22:03 CEST