pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Nested selects
2+ messages / 2 participants
[nested] [flat]

* Nested selects
@ 2009-04-08 02:34 Glenn Maynard <glennfmaynard@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Glenn Maynard @ 2009-04-08 02:34 UTC (permalink / raw)
  To: pgsql-sql

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.

-- 
Glenn Maynard



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Nested selects
@ 2009-04-09 15:13 Steve Midgley <science@misuse.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Steve Midgley @ 2009-04-09 15:13 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2009-04-09 15:13 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-08 02:34 Nested selects Glenn Maynard <glennfmaynard@gmail.com>
2009-04-09 15:13 Re: Nested selects Steve Midgley <science@misuse.org>

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