I have a website where people are saving highscores in games. I've been trying to assign a rank to a player's highscore by finding the number of other highscores that are higher. So if the query finds 5 people with higher highscores, the player's highscore rank will be 6.
The thing is that more than one score (for the same game) can be recorded in the database for every user. This is why I'm using GROUP BY user when I want to display only a player's best score in a game (not all of his scores in that game).
Here's the code I am using now. It doesn't work, since the query seems to always return 2 (like if it was always returning that there was only one score higher than the player's highscore). If I remove temp GROUP BY user, it returns an half-correct value, since counting all the scores (if a player as multiple scores in a game) from every player in a given game.
$count3 = mysql_result(mysql_query("SELECT COUNT(*) + 1 as Num FROM (SELECT * FROM ava_highscores WHERE game = $id AND leaderboard = $get_leaderboard[leaderboard_id] AND score > '$highscore2[score]') temp GROUP BY user");
When you use GROUP BY then COUNT returns a count of rows per group rather than a single count of all rows in the result set. Use COUNT(DISTINCT ...) instead. Also you don't actually need the inner select. You can write it all as a single query:
SELECT COUNT(DISTINCT `user`) + 1 AS Num
FROM ava_highscores
WHERE game = '3'
AND leaderboard = '5'
AND score > '1000'
Notes
score column is a numeric type (not a varchar type) so that the comparison works correctly.(game, leaderboard, score) will make the query more efficient. The order of the columns in the index is also important.