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@cam.ac.uk (here x and y are variables representing letters of
the alphabet).
Cheers,
James
\/\/\
