[SQL] Nested selects

[Glenn Maynard]

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] changing multiple pk's in one update

[Stuart McGraw]

Hello all, 

I have a table with a primary key column
that contains sequential numbers.

Sometimes I need to shift them all up or down
by a fixed amount.  For example, if I have
four rows with primary keys, 2, 3, 4, 5, I 
might want to shift them down by 1 by doing:

  UPDATE mytable SET id=id-1

(where "id" is the pk column) so that the pk's 
are now 1, 2, 3, 4.

When I try to shift them up by using +1 in the
above update statement, I get (not surprisingly)
a duplicate key error.  I also realize that the
-1 case above works only by luck.

So my question:
Is there some way, perhaps with ORDER BY, that
I can achieve the change I want with a single 
update statement?  (If I have an unused key
range large enough, I suppose I could update 
all the keys to that range, and then back to 
my target range but the requires two updates 
(there are a lot of foreign keys referencing 
these primary keys) and requires that I have 
an available range, so a single update statement
would be preferable.)

Thanks for any enlightenment.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql