mysql - Top 3 results from each player and sort by total -

mysql - Top 3 results from each player and sort by total -

i need come results shows sum of top 3 results each player , sort sum

example table:

david = 12 mike = 17 john = 20 bill = 20 david = 12 mike = 16 john = 18 bill = 20 david = 11 mike = 15 john = 16 david = 10 mike = 14 john = 16 david = 10 john = 15

results needs show follows:

john = 54 mike = 50 bill = 40 david = 35

i have in place rankings events (simple):

select playername, sum(`eventpoints`) `eventresults` grouping `playername` order sum(`eventpoints`) desc

i thinking next php code:

create table called top3events truncate top3events @ startup read through results of query above on table eventresults use counter , reset each new playername insert top3events when counter <= 3

run query:

select playername, sum(`eventpoints`) `top3events` grouping `playername` order sum(`eventpoints`) desc

not sure if right way go this

i guess no-one else wants tackle this. there's link in comments, i'd approach using mix of sql , code (php). solution assumes playername player's primary key, , there aren't big number of players

i'd first execute query (better players table, if available):

select distinct `playername` `eventresults`

i'd loop on query result, building series of queries i'd union:

select `sub`.`playername`, sum(`sub`.`eventpoints`) `pointssum` ( select `playername`, `eventpoints` `eventresults` `playername` = '$playername' order `eventpoints` desc limit 3 ) `sub`

if the calculation doesn't need performed every time view it, store result in summary table , utilize when viewing it.

using names in example, resulting query this:

select `sub`.`playername`, sum(`sub`.`eventpoints`) `pointssum` ( select `playername`, `eventpoints` `eventresults` `playername` = 'david' order `eventpoints` desc limit 3 ) `sub` union select `sub`.`playername`, sum(`sub`.`eventpoints`) `pointssum` ( select `playername`, `eventpoints` `eventresults` `playername` = 'mike' order `eventpoints` desc limit 3 ) `sub` union select `sub`.`playername`, sum(`sub`.`eventpoints`) `pointssum` ( select `playername`, `eventpoints` `eventresults` `playername` = 'john' order `eventpoints` desc limit 3 ) `sub` union select `sub`.`playername`, sum(`sub`.`eventpoints`) `pointssum` ( select `playername`, `eventpoints` `eventresults` `playername` = 'bill' order `eventpoints` desc limit 3 ) `sub` order `pointssum` desc

mysql sql greatest-n-per-group