Page 1 of 1

SUM only the best five results

Posted: Fri Feb 26, 2010 3:51 pm
by MarK (CZ)
Hi again! :)

I've been trying to find a solution to this for a long time but unfortunately no luck.

I have a series of race results that are measured as percentage to the winner. Now, the year's championship cup rank is determined by the sum of the best five results (highest percentage) or less, if the racer didn't attend five races yet.

So what I need to do, is get the SUM of the (max) top five results for every racer. Similar to this...

Code: Select all

SELECT racer, SUM(percentage) FROM results
GROUP BY racer
...but restricted only to the top five results.

Is there any way to do this in one MySQL query?

Thanks in advance!

Re: SUM only the best five results

Posted: Fri Feb 26, 2010 3:58 pm
by Eran
It's not completely straightforward, but it's possible. Check out this article which discusses choosing n-top results, and then run a SUM on the rows fetched - http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/

Re: SUM only the best five results

Posted: Fri Feb 26, 2010 6:06 pm
by MarK (CZ)
This stuff makes my head spin! :crazy: :lol:

But I hope I've put it together somehow, thanks! ;)