MySQL choose user location

Question

I have table users:

id | rating 1 4 2 -1 3 12 4 0 5 2 6 0 

How can I choose the user ranking order by id = N and that users with the same rating have the same place at the top?

UPD: I want to output:

If userid = 1 , his rank is 2 , if userid = 2 , his rank is 5 , if userid = 3 , rank is 1 , ... But if userid = 4 or 6 their rank are 4 , because the rates are the same.

Answer 1

I think you want to find a rating for asll user_id`s`:

 SELECT id, IF(rating <> @var_rating_prev, (@var_rank:= @var_rank + 1), @var_rank) AS rank, (@var_rating_prev := rating) AS rating FROM table_name a,(SELECT @var_rank := 0, @var_rating_prev := 0) r ORDER BY rating DESC; 

@ Sqlfiddle example

If you do not want to reorder the output, try the following:

 SELECT a.*, b.rank FROM test_table a INNER JOIN ( SELECT id, IF(rating <> @var_rating_prev, (@var_rank:= @var_rank + 1), @var_rank) AS rank, (@var_rating_prev := rating) AS rating FROM test_table a,(SELECT @var_rank := 0, @var_rating_prev := 0) r ORDER BY rating DESC ) b ON a.id = b.id ORDER BY a.id; 

Answer 2

 SELECT COUNT(*)+1 `rank` FROM table_name WHERE rating > (SELECT rating FROM table_name WHERE id = N)