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 Quote Link to comment 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 | +-------+------+ Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.