根据ORDER获取每一行的位置

Question

I have a highscore table that has three columns, name, highScore, rep. Currently I loop through the results ORDERED by rep. I have an $i est within the loop and this displays as rank eg:

SELECT highScore, name, rep FROM  table1 ORDER BY rep DESC

while ($row = $result->fetch_assoc()) 
        {
                echo "<tr>";
                echo "<td>" . $i . "</td>"; // rank
                echo "<td>" . $row["name"] . "</td>"; // name
                echo "<td>" . $row["rep"] . "</td>"; // rep
                echo "<td>" . $row["highScore"] . "</td>"; // high score
                echo "</tr>";
                $i++;
            }
        }

Currently this works without fault. The problem is that i have now implemened a "sort by" button which changes the SQL to ORDER to highScore instead of rep. the only problem is that the Rank does not match now. Is there a way of getting and echoing the position of each row based on rep and then seslcting and ordering based on highScore?

Answer 1

select * from 
(
  SELECT highScore, name, rep, @rank := @rank + 1 as rank 
  FROM table1, (select @rank := 0) r
  ORDER BY rep DESC
) alias_name
order by highscore desc

rank contains your rank by rep and the table is ordered by highscore now.