List top 5 within each group by

[ded]

How do I show the totals for the top 5 for each group by?

 

Example:

Joe 29

Joe 25

Joe 24

Joe 23

Joe 31

Bill 20

Bill 29

Bill 2

Bill 8

Bill 22

Scott 25

Scott 14

Scott 19

Scott 25

Scott 29

Mike 23

Mike 19

Mike 22

Mike 28

Mike 23

Bob 22

Bob 26

Bob 22

Bob 24

Bob 21

 

Total points would show the following:

Joe = 131

Mike = 115

Bob = 115

Scott = 112

Bill = 81

 

I want to rank the people by their top 3 points

Joe = 85

Scott = 79

Mike = 74

Bob = 72

Bill = 71

 

 

How is this done in SQL?

Is it possible?

 

This is my current code

SELECT *, SUM(`nationalpoints`)  
FROM `tournamentresults` 
GROUP BY `playername` 
ORDER BY SUM(`nationalpoints`) DESC

 

Regards,

David Hascup

 

[cpd]

You could use a partition.

 

SELECT *, SUM(nationalpoints) OVER(PARTITION ON playername ORDER BY id) AS TotalPoints FROM tournamentresults ORDER BY TotalPoints ASC

[Barand]

This question is almost identical to a previous post of yours (http://www.phpfreaks.com/forums/index.php?topic=357894.0).

 

If you read my reply to that one it should give you you a insight into solving this one for yourself.

[cpd]

I'm not fond of your method Barand as it requires the MySQL Server to carry out a select for each row returned by the primary select statement. Almost similar to RBAR.

[fenway]

See here.