Mysql Sum using ranking (best x scores from all rounds for all players)

Question

1. Some help please. I have tested many examples in this forum without success.

This script works fine but I now wish to limit the summed round scores to best 14 rounds.

SELECT 
RoundScoreCard.idPlayerDetails as ID,
#concat(PlayerFirstName," ",PlayerLastName)as Name,
sum(RoundScoreCardPlayerPoints) as Total 

from RoundScoreCard 

join PlayerDetails on RoundScoreCard.idPlayerDetails = PlayerDetails.idplayerdetails 
group by RoundScoreCard.idPlayerDetails 
order by Total DESC 

Test with this single sub query works fine;

select sum(RoundScoreCardPlayerPoints) 
FROM 
(select RoundScoreCardPlayerPoints
from RoundScoreCard
where RoundScoreCard.idPlayerDetails = @player 
order by RoundScoreCardPlayerPoints desc 
limit 14) as subquery
;

Have battled for about a week modifying every example I can find but just cant get this to work listing all players. I can show a multitude of attempts but this would confuse this question for others. (I do have a solution using a test for records and then an IF statement but it seems excessive and costly on time.)

2. Also; out of interest, is it possible to have multiple limit statements? i.e. what if I only wanted the top 3 players with their best 14 results from all the rounds?

Example http://sqlfiddle.com/#!9/f393a6/3

All the best for the new year.

---

OK - so I have progressed a bit further with the help of this article; https://www.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in-mysql.html

Thanks Rob Gravelle

Limiting my top scores to 2 for each player

My Sql is now as follows:

Select idRoundScorecard, RoundScoreCardPlayerPoints,idPlayerDetails
 FROM
 (
   SELECT idRoundScorecard, RoundScoreCardPlayerPoints, idPlayerDetails,
   @player_score_rank := IF(@current_player = idPlayerDetails, 
                         @player_score_rank + 1, 
                         1
                      ) AS player_score_rank,
   @current_player := idPlayerDetails
   FROM RoundScoreCard
    ORDER BY idPlayerDetails, RoundScoreCardPlayerPoints DESC
 ) ranked
 WHERE player_score_rank <= 2;

Now I have to sum and rank again?

Answer 1

Here is my solution http://sqlfiddle.com/#!9/4f4fe7/9

select idPlayerDetails as ID, concat(PlayerFirstName," ",PlayerLastName)as Name,sum(Points) as Points
from
(Select idRoundScorecard, RoundScoreCardPlayerPoints as Points,idPlayerDetails
 FROM
 (SELECT idRoundScorecard, RoundScoreCardPlayerPoints, idPlayerDetails,
   @player_rank := IF(@current_player = idPlayerDetails, 
                         @player_rank + 1, 
                         1
                      ) AS player_rank,
   @current_player := idPlayerDetails
   FROM RoundScoreCard
    ORDER BY idPlayerDetails, RoundScoreCardPlayerPoints DESC
 ) ranked
 WHERE player_rank <= 5 )summed
natural join PlayerDetails
group by idPlayerDetails
order by Points desc
 ;