I have a table of ID Vs Points, I want to return the Rank of the required id according to his points. I am thinking I can do this if i can sort the table ascending according to points then select the row number of the requited id. the problem is I don't know how to perform both operations (sort the table then select row number) in one query.
Note: I am using php prepared statement to perform the query.
any help please regarding how to perform the two operations in one step.
to return the rank of any ID. example.. Where Id = y --> it returns "2". (the rank in the table)
I performed the below code and it sorts the table and gives a rank correctly, my need now is to select the required ID (where ID = x). how to do that?
SELECT ID, Points, @rank:[email protected]+1 AS rank From Cars ORDER BY Points DESC;
this example help you
mysql> SELECT ID,points, @curRank := @curRank + 1 as rank from gg ,(SELECT @curRank := 0) r order by points; +------+--------+------+ | ID | points | rank | +------+--------+------+ | w | 4 | 1 | | x | 12 | 2 | | y | 15 | 3 | | z | 30 | 4 | +------+--------+------+ 4 rows in set (0.00 sec)
You can implement the official ANSI rank function in MySQL using a subquery:
select t.*, (select 1 + count(*) from t t2 where t2.points < t.points ) as rank from t;
This can also be done using variables, but it is rather complicated.