How to get the rank of a row?

Question

Hi

Yesterday I posted a similar (or the same?) Question yesterday, but it seemed to me that I needed to post a new question, since I have a short but clear question.

I have the following table.

id point 1 30 2 30 3 29 4 27 5 28 6 26 

what I want:

• get all users by rank. users # 1 and # 2 must have 1 as their rank value because they both have 30 points

• I want to request a rank identifier by user id. I like to get 1 as a result of my rank when I request users # 1 and # 2 because both of them have 30 points.

Added: 3/18

I tried Logan request but got the following result

 id point rank 1 30 1 2 30 1 3 29 3 4 27 5 5 28 4 6 26 6 

Answer 1

The recommended subquery approach will scale quadratically. http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ shows a much more efficient approach with custom variables. Here is an untested adaptation to your problem:

 @points := -1; // Should be an impossible value. @num := 0; SELECT id , points , @num := if(@points = points, @num, @num + 1) as point_rank , @points := points as dummy FROM 'users' ORDER BY points desc, id asc; 

Answer 2

Just calculate how much more points they have than them.

 select count(1) from users where point > (select point from users where id = 2) group by point 

This will give you the number of people who have more points for this user. Thus, for user 1 and user 2, the result will be 0 (zero), which means that they are the first.

Answer 3

When I needed to do something like this, I created a view that looked like this:

 CREATE VIEW rankings_view AS SELECT id , point , (select count(1) from points b where b.point > a.point) +1 as rank FROM points as a; 

This suggests that the source table was called dots, obviously. You can then get the rank of any identifier or identifier corresponding to any rank by querying the submission.

EDIT

If you want to count the number of different point values ​​above each point value instead of the number of records with points above the current point value, you can do something like:

 CREATE VIEW rankings_view2 AS SELECT id , point , (SELECT COUNT(1) +1 AS rank FROM ( SELECT DISTINCT point FROM points b WHERE b.point >a.point )) FROM points AS a; 

Note

Some of the solutions presented certainly work better than this. They are specific to mysql, so I cannot use them for my actions. My application has at most 128 objects for ranking, so this works pretty well for me. However, if you can have many lines, you can look at using one of the other solutions presented here, or limit the scope of the rating.

Answer 4

The OP would like rank numbers to be omitted if they were previously duplicated points with the same rank. For example. below shows how 2 is skipped because rank 1 appears twice.

 id point rank 1 30 1 2 30 1 3 29 3 4 27 4 5 28 5 6 26 6 

This can be achieved by modifying the btilly code as follows:

 set @points := -1; // Should be an impossible value. set @num := 0; set @c := 1; SELECT id , points , @num := if(@points = points, @num, @num + @c) as point_rank , @c := if(@points = points, @c+1, 1) as dummy , @points := points as dummy2 FROM `users` ORDER BY points desc, id asc; 

Answer 5

In MySQL 8, you can use a window function like:

 SELECT id, score, rank() over (order by amount) as ranking FROM SomeTable 

If you need to select only one row, use a subquery:

 SELECT id score, ranking FROM ( SELECT id, score, rank() over (order by score) as ranking FROM SomeTable ) t WHERE id = ? 

Answer 6

 SET @rank = 0, @prev_val = NULL; SELECT id, @rank := IF(@prev_val=points,@rank,@rank+1) AS rank, @prev_val := points AS points FROM users ORDER BY points DESC, id asc; 

Table: Users