Jump to content

Top 3 records for each person


ded

Recommended Posts

I have a database that results records for several thousand players.  I need a report that will show the following.

Rank the top players based on their top 3 finishes.

 

I have the following for the full rankings which is working perfect

SELECT *, SUM(`nationalpoints`)  FROM `tournamentresults` WHERE `gender` = '$gender' and `affiliation` <> ' ' and substr(`date`, 1, 4) = '2012' GROUP BY `playername` ORDER BY SUM(`nationalpoints`) DESC

 

The above gathers ALL nationalpoints for the player.  I want to just grab the highest 3 nationalpoints for each player and then rank the players by that number.

 

Example:

Joe

12

Joe

10

Joe

8

Joe

16

Joe

15

 

Bob

8

Bob

16

Bob

18

Bob

18

Bob

15

 

Mike

24

Mike

15

Mike

16

Mike

4

Mike

2

 

Sam

34

Sam

12

Sam

5

Sam

24

Sam

1

 

David

24

David

22

David

25

David

14

David

21

 

 

National Rankings from the table above would be the following:

1. David = 106

2. Sam = 76

3. Bob = 75

4. Joe = 61

4. Mike = 61

 

With the idea I need, the rankings would be

1. David = 71

2. Sam = 70

3. Mike = 55

4. Bob = 52

5. Joe = 43

 

 

Regards,

David

 

Link to comment
https://forums.phpfreaks.com/topic/261112-top-3-records-for-each-person/
Share on other sites

Try

mysql> SELECT name, SUM(score) as tot
    -> FROM scores s
    -> WHERE
    -> (
    ->   SELECT COUNT(*) FROM scores
    ->   WHERE name = s.name
    ->   AND score >= s.score
    -> ) < 4
    -> GROUP BY name
    -> ORDER BY tot DESC;
+-------+------+
| name  | tot  |
+-------+------+
| David |   71 |
| Sam   |   70 |
| Mike  |   55 |
| Bob   |   52 |
| Joe   |   43 |
+-------+------+

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.