Dear Dave,
CREATE VIEW best_in_school_method3 AS
SELECT competition_name, academic_year_beginning, centre_number,
entry_id, total_score, (true) AS best_in_school FROM challenge_entries
ce1
WHERE total_score =
(SELECT MAX(total_score) FROM challenge_entries ce2
WHERE ce1.competition_name=ce2.competition_name
AND ce1.academic_year_beginning=ce2.academic_year_beginning
AND ce1.centre_number=ce2.centre_number
)
Thanks! That works much better, as you can see here:
http://explain.depesz.com/s/Jz1
If you don't actually need to have the view for other purposes, and just
want to solve the original problem (listing certificates to be issued), you
can do it as a direct query, e.g.
I'll keep the view, please.
PostgreSQL also has a proprietary extension SELECT DISTINCT ON which has
a much nicer syntax, but unlike the above it will only show one
(arbitrarily selected) pupil per school in the event of a tie, which is
probably not what you want :-)
Indeed not, that's disastrous here.
Looking at the schema, the constraint one_challenge_per_year is redundant
with the primary key.
Oh, yes, thanks. It's a legacy from an earlier approach.
P.S. Small world ... did my undergrad there, back when @cam.ac.uk email
went to an IBM 3084 mainframe and the user ids typically ended in 10 :-)
Heh. The people with only two initials are generating bignums these days: I
know xy777@xxxxxxxxx (here x and y are variables representing letters of
the alphabet).
Cheers,
James
\/\/\
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
