Jeffrey Ratcliffe
<[EMAIL PROTECTED]> wrote:
The following query works fine (and is quick), but I can't help
thinking that it should be possible to it with a single SELECT and a
self join.
CREATE TEMPORARY TABLE temp (eid INT, P DOUBLE);
INSERT INTO temp (eid, P)
SELECT eid, MIN(P)
FROM barforce_1
GROUP BY eid;
SELECT a1.eid, a1.P, a2.subcase
FROM temp a1, barforce_1 a2
WHERE a1.P = a2.P
ORDER BY a1.eid;
DROP TABLE temp;
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;
I'm not sure your query does what you really mean though. Do you
intentionally join on P, rather than eid? Consider:
eid P subcase
--------------------
1 10 a
1 20 b
2 10 c
2 20 d
The result of either query (the two should be equivalent) would be
1 10 a
1 10 c
2 10 a
2 10 c
Is that the result you had in mind?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
