Finding rank of a particular from a query result

[iamnot]

I am using mysql to fetch this result from the database.I need to caluclate the rank of a prticular user id from this result.What is the easy and smart way of doing it?

SELECT a.UserId,d.AreaId,d.SubjectId, SUM( a.Marks ) AS sum, COUNT( a.Marks ) AS count, SUM( a.Marks ) / COUNT( a.Marks ) AS avg, b.PublicGroupId
FROM progressreport a, publicgroupmap b, qpaper c, subjects d,areas e
WHERE b.PublicGroupId =  '10'
AND a.UserId = b.UserId
AND c.QpaperId = a.QpaperId
AND c.SubjectId = d.SubjectId
AND d.AreaId='2'
GROUP BY d.AreaId,a.UserId
ORDER BY avg DESC

`

[diafol]

If you're looping over this in php to get out the results:

$num = 0; //previous average 
$pos = 1; //rank
$x = 1;  //counter
if(mysql_num_rows($result)){
    while($data = mysql_fetch_assoc($result)){
        if($data['ave'] != $num && $num != 0)$pos = $x; 

        //do your display stuff here using $pos as your rank
        //This should provide duplicate ranks for duplicate averages
        //It should then do something like this: 1,2,3,3,5,6,7,7,7,10
        //example:
        echo "<p>$pos: {$data['ave']}</p>";

        $num = $data['ave'];
        $x++;
    }
}

Not tested!!!

Even if this doesn't work, it's a trivial thing for PHP. There may be a way to do it in MySQL, but I don't know if it would be as easy. IMO, use MySQL to retrieve data in as simple a format as possible. Cosmetic stuff or calculations that aren't required for ordering/ filtering/other fields, can be left to php if you're using a loop. Feel free to correct me.