计算用户的排名

Question

I want to calculate rank using MySQL. I have a table called result

result_id    test_id    exam_id    user_id    percentage
   1            5          6          50          57
   2            5          6          58          76 
   3            5          6          65          42

I want to calculate the rank of the user according to his user_id and test_id like user_id(58) has 1 rank user_id(50) has 2 and so on

I tried query like

select percentage  from result where test_id=$test_id(i.e 5) and user_id=$user_id(i.e 58)

but it gives 76 and doesn't give the rank

I also tried

select percentage from result where test_id=$test_id(i.e 5) 

but it gives me 57,76,42

Is there any way by which I can calculate the rank of the user?

Answer 1

You can simply use ORDER BY percentage DESC. Try this...

$con = mysqli_connect("host","user","password","db_name");
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = 'SELECT * FROM result ORDER BY percentage DESC';
$result = mysqli_query( $con, $sql );
if (!mysqli_query($con, $sql)) {
    die('Error: '. mysqli_error($con));
}
$i = 1;
while($row = mysqli_fetch_array($result)) {
    echo 'User ID ' . $row['user_id'] . ' has rank  ' . $i . ' and percentage ' . $row['percentage'] . '</br>';
    $i++;
}
mysql_close($con);