2009/4/9 Glenn Maynard
<glennfmaynard@gmail.com> (This is related to an earlier post on -sql.)
I'm querying for the N high scores for each game, with two tables:
scores and games.
CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
game_id INTEGER REFERENCES game (id));
-- test data: 1000 games, 100000 scores
INSERT INTO game (id) select generate_series(1,1000);
INSERT INTO score (game_id, score) select game.id, random() from game,
generate_series(1,100);
CREATE INDEX score_idx1 ON score (game_id, score desc);
ANALYZE;
How about
select s1.*
from score s1 join score s2 on s1.game_id=s2.game_id and s2.score >= s1.score
group by s1.*
having count(s2.*) <= N
Note: you can have problems if you have same scores - you will loose last group that overlap N
In any case, you don't need to join game since all you need is game_id you already have in score.
P.S. EXPLAIN ANALYZE could help
Best regards, Vitalii Tymchyshyn