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

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;