当前位置: 动力学知识库 > 问答 > 编程问答 >

how to sort and get row number in one MYSQL Query

问题描述:

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.

Data Samples:

ID Points

x 12

y 15

w 4

z 30

Required:

to return the rank of any ID. example.. Where Id = y --> it returns "2". (the rank in the table)

Update

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?

SET @rank=0;

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: