2009/4/9 Glenn Maynard <glennfmayn...@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
