Ranking query
Posted: Tue Jul 25, 2006 12:40 pm
I wanted to make a small ranking query which gave people their rank based on the score they have throughout the site. The query that I have so far is:
Basically it counts how many people have a higher score than them and then uses that as a rank. It works great until you have people with an equal score, it will then do this:
Rank 1 : 100 points
Rank 2 : 98 points
Rank 2 : 98 points
Rank 4 : 95 points
I would like it so it goes Rank 1, 2, 3, 4. It doesnt work because the order by is just ordering the ranks which the count isnt counting. I hope you understand what I mean and that it is possible with a single query.
Thanks,
someberry.
Code: Select all
SELECT count(id) AS rank
FROM `country`
WHERE `score` >= '" . $user['s'] . "'
ORDER BY `score` DESC, `id` ASCRank 1 : 100 points
Rank 2 : 98 points
Rank 2 : 98 points
Rank 4 : 95 points
I would like it so it goes Rank 1, 2, 3, 4. It doesnt work because the order by is just ordering the ranks which the count isnt counting. I hope you understand what I mean and that it is possible with a single query.
Thanks,
someberry.