Angelo Brigante, Jr. wrote:
SELECT master.nameLast, sum(batting.hr)
FROM master,batting
WHERE master.playerid = batting.playerid
GROUP BY master.nameLast, batting.playerid
HAVING sum(batting.hr) > 500
ORDER BY sum(batting.hr) DESC;
In both postgresql and Microsoft Access 2000 this query takes 2 and 3
seconds to execute respectively. In sqlite3 this query took 34 minutes
and 10 seconds. sqlite3 and postgresql are on the same machine.
What's the schema? Do you have an index? Have you
tried:
CREATE INDEX idx1 ON batting(playerid);
Here's another approach to the query:
SELECT nameLast, hrtotal
FROM (SELECT playerid, sum(hr) AS hrtotal
FROM batting
GROUP BY playerid) AS a, master
WHERE hrtotal>=500 AND master.playerid=a.playerid;
In the second approach it would help to have an
index like this:
CREATE INDEX idx2 ON master(playerid);
But in the second approach, the index is not as important.
I think the second approach will be much faster than the
first.
This query below quickly prints out the last names of every player who
has hit 500 or more career homeruns, but I can't figure out how to get
it to print their career total.
The query above does this, I think.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
