SUM only the best five results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

SUM only the best five results

Post 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SUM only the best five results

Post 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/
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Re: SUM only the best five results

Post by MarK (CZ) »

This stuff makes my head spin! :crazy: :lol:

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