Jeffrey Ratcliffe
<[EMAIL PROTECTED]> wrote:
On 04/07/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:
select a1.eid, a1.P, a2.subcase
from barforce_1 a1 join barforce_1 a2 on (a1.P = a2.P)
where not exists
(select * from barforce_1 a3 where a3.eid = a1.eid and a3.P <
a1.P) order by a1.eid;
Excellent. The only trouble is that I don't understand why it works. I
assume that
a3.P < a1.P
is replacing the MIN(P), but I don't following what is happening.
Would you mind explaining?
For each unique eid you look for a record with this eid where P is
smallest. Another way to express "smallest" is to say: there doesn't
exist a record with the same eid and a smaller value of P. Which is
precisely what my "not exists" clause is saying.
I'm not sure your query does what you really mean though. Do you
intentionally join on P, rather than eid? Consider:
To cover all cases, I would join on both, but as P is in my case a
double, there are almost never duplicates.
But if there _are_ duplicates, do you or don't you expect the result
I've shown? In that result, you might get a record (eid, P, subcase)
where subcase value comes from a record with a different eid (but which
just happens to have the same P). If you don't, the query could be made
simpler:
select eid, P, subcase
from barforce_1 a
where not exists
(select * from barforce_1 a1 where a1.eid = a.eid and a1.P < a.P)
order by eid;
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
