如何排序mysql数据并找到位置？ [关闭]

Question

I have mysql data of 3 rows (user_id, exam_id, marks). I want to find each user ranking position based on marks and exam_id . How will be the SQL query with php code for this. Please help me. Thanks in advance.

Here is partial code

$p = "SELECT * FROM IDTABLE WHERE EID = '$info[EID]'";
$rowp = mysqli_query($conn, $p);
while (($ret = mysqli_fetch_assoc($rowp)) > 0) {
    $q = "SELECT * FROM INFOTABLE WHERE EID = '$ret[EID]'";
    $rowq = mysqli_query($conn, $q);
    while (($retq = mysqli_fetch_assoc($rowq)) > 0) {                                
        $user[$ret['EID'] . $retq['UID']] = $retq['Marks'];                                
    }
}
arsort($user);
var_dump($user);

Answer 1

First of all you should consider a few things:

• You should always better search for an already answered question before posting a new one (it's part of stack overflow's code)
• Bring more information of what you want, being as clear as possible
• Giving more details (for example: you sql structure)

However, I will try to give you a hint - Hope it helps!

• For example you want to order your user_id based on their marks:

  select user_id from my_table order by "marks" asc;

• For example you want to order your user_id based on their marks and exam_id :

  select user_id from my_table order by "marks", "exam_id" asc;

• For example you want to create a kind of ranking based on users's marks:

  SET @rank := 0; SELECT *, @rank := @rank + 1 AS rank FROM my_table order by marks ASC;

• For example you want to get the ranking number from a specific

  SET @rank := 0; SELECT * from ( select *, @rank := @rank + 1 FROM my_table order by marks ASC ) AS rank where user_id = 2;

Hope this helps! You could get more information about mysql's ranking here

Regards,