From: Steve Midgley <science@misuse.org>
To: pgsql-sql@postgresql.org
Subject: Re: Nested selects
Date: Thu, 09 Apr 2009 08:13:45 -0700
Message-ID: <49DE10A9.2000400@misuse.org> (raw)
In-Reply-To: <20090409032029.1F26C1E80C9F@mx2.hub.org>
References: <20090409032029.1F26C1E80C9F@mx2.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: science@misuse.org
Subject: Re: Nested selects
In-Reply-To: <49DE10A9.2000400@misuse.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox