Table

mysql>                SELECT player_ID,player_name,HP,@rank:=@rank+1 As Rank
    ->                FROM player_profile ORDER BY HP DESC;
+-----------+-------------+-----+------+
| player_ID | player_name | HP  | Rank |
+-----------+-------------+-----+------+
| #PLY0003  | dsa         | 100 |    1 |
| #PLY0001  | Final       |  70 |    2 |
| #PLY0002  | Test        |  70 |    3 |
| #PLY0005  | New         |  60 |    4 |
| #PLY0004  | Alpha       |  10 |    5 |
+-----------+-------------+-----+------+
5 rows in set (0.00 sec)