Re: Rapidly finding maximal rows

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(at)cam(dot)ac(dot)uk (here x and y are variables representing letters of
the alphabet).

Cheers,

James
\/\/\