How to get a row rank?

Question

HI,

I actually posted similar (or same?) question yesterday, but I thought I need to post a new question since I have 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:

1. get all the users order by rank. user #1 and #2 should have 1 as their rank value because they both have 30 points

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

Added on: 3/18

I tried Logan's query, 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

Just count how many people have more points then 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 that have more points for the given user. So for user 1 and user 2 the result will be 0 (zero) meaning they are first.

Answer 2

The subquery approach that you have seen recommended will scale quadratically. http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ shows a much more efficient approach with user 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 3

The OP would like to have rank numbers skipped if their previously were duplicate points with the same rank. E.g. below see 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 btilly's 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 4

When I needed to do something similar, 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 assumes that the original table was named points, obviously. Then you can get the rank of any id, or the id corresponding to any rank, by querying the view.

EDIT

If you want to count the number of distinct point values above each point value instead of the number of entries with point values 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 other solutions presented definitely perform better than this one. They're mysql specific, so I can't really use them for what I'm doing. My application has, at most, 128 entities to rank, so this works well enough for me. If you might have tons of rows, though, you might want to look at using one of the other solutions presented here or limiting the scope of the ranking.

Answer 5

In mysql 8 you can use 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