ded Posted April 17, 2012 Share Posted April 17, 2012 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 More sharing options...
Barand Posted April 18, 2012 Share Posted April 18, 2012 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 | +-------+------+ Link to comment https://forums.phpfreaks.com/topic/261112-top-3-records-for-each-person/#findComment-1338357 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.