On 04/07/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
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.
Wow. It is counter-intuitive (to me at least) that this is quick.
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;
Even better. Thanks again. But in the case where there ARE duplicates:
CREATE TEMPORARY TABLE temp (eid, P, subcase);
INSERT INTO temp (eid, P, subcase) VALUES (1, 1, 'a');
INSERT INTO temp (eid, P, subcase) VALUES (1, 1, 'b');
INSERT INTO temp (eid, P, subcase) VALUES (2, 2, 'a');
INSERT INTO temp (eid, P, subcase) VALUES (2, 2, 'b');
SELECT eid, P, subcase
FROM temp a1
WHERE NOT EXISTS (
SELECT *
FROM temp a2
WHERE a2.eid == a1.eid AND a2.P < a1.P
)
ORDER BY eid;
DROP TABLE temp;
I get:
1,1,a
1,1,b
2,2,a
2,2,b
I would like:
1,1,a (or 1,1,b it doesn't matter which)
2,2,a (or 2,2,b)
How can I modify the query so as only one minimum per element (it
doesn't matter which one)? Is there any way of getting DISTINCT to
only operate on eid and P but not subcase?
Thanks
Jeff
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
