Re: SQL help

>> I have not passed Cello's year to get used to thinking in sets, so
I do
not have the skill to write an appropriate subquery for the following specification .. <<

At least you get time for free. Just sit and it comes to you.

Looking at this, I am confused. There is an Owners table, but I do not see an owner identifier in it. I would assume that onwers are entities and not relationships. What exactly do you want to get back from the query?

Also "entity_id" is a terrible name; can there be an identifier that references a non-entity? It is too vague -- identifier of exactly what? I also did not recognize the dialect of SQL you were using. Considering how many of them I have worked on, that is saying something. Minor nit: DEFAULT comes before NOT NULL in Standard SQL.

>> The longest owner: MIN(starting) AND starting<=gd AND (ending IS
NULL OR ending>=gd) <<

The pseudo-code is a bit hard to understand; is this what you meant?

SELECT bh_id, entity_id, starting, percent, ending   FROM Owners
 WHERE :gd BETWEEN (SELECT MIN(starting) FROM Owners)

               AND COALESCE (endding, CURRENT_TIMESTAMP)

>> If there ties, MAX(percent) among them <<

Nest the first query inside another one ..

SELECT X.bh_id, X.entity_id, X.starting, X.percent, X.ending

   FROM (<>)AS X(bh_id, entity_id, starting, percent, ending)  WHERE X.percent

• (SELECT MAX(percent) FROM (<>));

This is where you really want a WITH operator

>> If there are still ties, entity_id = :ge_id if it exists else
longest membership: MIN(entity_id), The last is guaranteed to be unique. <<

I am reading that to mean:

SELECT bh_id, entity_id, starting, percent, ending   FROM <??> -- horrible nested thing
 WHERE entity_id = COALESCE (:ge_id, (SELECT MIN(entity_id) FROM <??>))

Sounds like more nesting, but if entity_id has to do with memberships, why isn't it named "membership_nbr" or something that explains itself? Received on Thu Oct 07 2004 - 19:51:08 CEST