Message-ID: <49DE10A9.2000400@misuse.org>
Date: Thu, 09 Apr 2009 08:13:45 -0700
From: Steve Midgley <science@misuse.org>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Nested selects
References: <20090409032029.1F26C1E80C9F@mx2.hub.org>
In-Reply-To: <20090409032029.1F26C1E80C9F@mx2.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

pgsql-sql-owner@postgresql.org wrote:
> Date: Tue, 7 Apr 2009 22:34:38 -0400
> From: Glenn Maynard <glennfmaynard@gmail.com>
> To: pgsql-sql@postgresql.org
> Subject: Nested selects
> Message-ID: <d18085b50904071934g7ad206f1i14ac05f7bd29f05e@mail.gmail.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