Get a ranking based on the sum of a user's score?

[freaksauce]

Each score submitted inserts a new row, I have the following query to order the users scores by the sum of their scores.

 

SELECT *, SUM(score) as sum
FROM `scores`
GROUP BY user_id
ORDER BY sum DESC

 

Now I need some way of getting their rank by counting the number of rows greater than the score. So something like:

 

SELECT *, SUM(score) as sum, COUNT(sum) as rank
FROM `scores`
GROUP BY user_id
WHERE sum > 100
ORDER BY sum DESC

 

Does that make any sense?

 

Server version: 5.0.24a-community

PHP Version 5.1.6

[fenway]

Check out this resource (it was in one of the stickies).

[Barand]

<?php 
mysql_query ("CREATE TEMPORARY TABLE tmpx SELECT user_id, SUM(score) as score FROM ratings GROUP BY user_id");
mysql_query ("CREATE TEMPORARY TABLE tmpy SELECT user_id, SUM(score) as score FROM ratings GROUP BY user_id");

$sql = "SELECT x.user_id, x.score,
        (SELECT COUNT(*)+1 FROM tmpy WHERE score > x.score) as position
         FROM tmpx x
         ORDER BY position";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list ($pid, $sc, $pos) = mysql_fetch_row($res))
{
    echo "$pid ($sc) : $pos <br/>";
}
?>