sum the top three

Dear All,

I hope this is the correct group to ask the following question.

I have a table called results which has entries of the form:

date ; name ; score.

I want a query something like the following ...

SELECT name, sumTopThree(score) as total
FROM results
GROUP BY name
ORDER BY total DESC

Clearly what I need is to define the function sumTopThree which is
intended to be the sum of the top three results per player. Is there
any way to do this?

Thankyou in advance,

Geoff Ostrin.

Hmm, I wonder if CP will be along in a minute ;-)

Anyway, see if you can adapt this query to suit your requirements...

SELECT lastname, sum(score) totals from (
select p1.lastname, p1.firstname, score, rank
from people p1
join
(select count(*) rank,
a.lastname, a.firstname
from people A join
people B
on (A.lastname = B.lastname
and A.score = B.score
and A.firstname <= B.firstname)
or (A.lastname = B.lastname
and A.score < B.score)
group by A.lastname, A.firstname) N
on p1.lastname= N.lastname and
p1.firstname = N.firstname
order by p1.lastname, rank) xxx
where rank <= 3
group by lastname
order by totals desc, lastname;

On 30 Jan, 13:54, "Geoff" <geoffost...@yahoo.co.uk> wrote:
> Dear All,
>
> I hope this is the correct group to ask the following question.
>
> I have a table called results which has entries of the form:
>
> date ; name ; score.
>
> I want a query something like the following ...
>
> SELECT name, sumTopThree(score) as total
> FROM results
> GROUP BY name
> ORDER BY total DESC
>
> Clearly what I need is to define the function sumTopThree which is
> intended to be the sum of the top three results per player. Is there
> any way to do this?
>
> Thankyou in advance,
>
> Geoff Ostrin.