[sqlite] self join instead of temporary table

[Jeffrey Ratcliffe]

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;

I had the following in mind, but it doesn't work (the subcase column
contains the max subcase value, rather than the subcase at which the
minimum P value occurred):

SELECT a1.eid, MIN(a1.P), a2.subcase
FROM barforce_1 a1, barforce_1 a2
WHERE a1.P = a2.P
 GROUP BY a1.eid;

Any ideas?

Jeff Ratcliffe

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------