Re: Nested selects

pgsql-sql-owner(at)postgresql(dot)org wrote:
> Date: Tue, 7 Apr 2009 22:34:38 -0400
> From: Glenn Maynard <glennfmaynard(at)gmail(dot)com>
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Nested selects
> Message-ID: <d18085b50904071934g7ad206f1i14ac05f7bd29f05e(at)mail(dot)gmail(dot)com>
>
> I'm deriving high scores from two tables: one containing data for each
> time a user played (rounds), and one containing a list of stages:
>
> CREATE TABLE stage (id SERIAL NOT NULL PRIMARY KEY, name VARCHAR);
> CREATE TABLE round (id SERIAL NOT NULL PRIMARY KEY, score REAL,
> stage_id INTEGER REFERENCES stage (id));
> INSERT INTO stage (name) VALUES ('stage 1'), ('stage 2'), ('stage 3');
> INSERT INTO round (stage_id, score) VALUES
> (1, 100), (1, 150), (1, 175),
> (2, 250), (2, 275), (2, 220),
> (3, 350), (3, 380), (3, 322);
>
> SELECT r.* FROM round r
> WHERE r.id IN (
> -- Get the high scoring round ID for each stage:
> SELECT
> (
> -- Get the high score for stage s:
> SELECT r.id FROM round r
> WHERE r.stage_id = s.id
> ORDER BY r.score DESC LIMIT 1
> )
> FROM stage s
> );
>
> This works fine, and with a (stage_id, score DESC) index, is
> reasonably fast with around 1000 stages. round may expand to millions
> of rows.
>
> Unfortunately, it doesn't generalize to getting the top N scores for
> each stage; LIMIT 2 isn't valid ("more than one row returned by a
> subquery used as an expression").
>
> I fiddled with putting the inner results in an array, without much
> luck, and I'm not sure how well that'd optimize. Having the results
> in any particular order isn't important. (In practice, the inner
> select will often be more specific--"high scores on the west coast",
> "high scores this month", and so on.)
>
> This seems embarrassingly simple: return the top rounds for each
> stage--but I'm banging my head on it for some reason.
>
>
How about this:

select round.*, stage.name from round
left join stage on stage.id = round.stage_id
ORDER BY round.score DESC;

Steve