MySQL Rank my total score

Question

I have a table that looks something like below without the rank field. I want to put that rank field as it is in the table below. How can I achieve it in MySQL?

#   name    aa   bb   cc   total   |  rank
1    name1   20   20   30     70   |   1
2    name2   10   20   30     60   |   2
3    name3   20   10   25     55   |   3
4    name4   20   20   30     70   |   1
5    name5   10   10   20     40   |   4

possible duplicate of [Rank function in MySQL](http://stackoverflow.com/questions/3333665/rank-function-in-mysql) — Raptor, Mar 04 '15 at 06:44
the solution in your duplicate doesnt involve field with same value. Also thats not a good ranking as you can see in that solution it just sorted the table and added an incremented value. Not good — Ikong, Mar 04 '15 at 06:59
You can add handling mechanism of same total yourself, please try on your own, so that you can learn from the progress. — Raptor, Mar 04 '15 at 07:01
my goodness... whats the purpose of asking and displaying my answer when I never have the chance to get enough to do this... cmon.. — Ikong, Mar 04 '15 at 07:02
DBA.SE also has another post about the exact topic: http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table — Raptor, Mar 04 '15 at 07:02
now why does it involve db structure when you can see the actual table and just query 1 table...omg — Ikong, Mar 04 '15 at 07:22

score 1 · Accepted Answer · answered Mar 04 '15 at 07:09

Here is a way to do it, the result will be sorted with respect to the total.

mysql> create table test (id int, name varchar(100),total int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values 
    -> (1,'name1',70),
    -> (2,'name2',60),
    -> (3,'name3',55),
    -> (4,'name4',70),
    -> (5,'name5',40);
Query OK, 5 rows affected (0.02 sec)


select 
id,
name, 
case 
when @cur_rank = total then @rank 
else @rank := @rank + 1 
end as rank, 
@cur_rank := total as total 
from test ,(select @rank:=0, @cur_rank:=0)r 
order by total desc ;


+------+-------+------+-------+
| id   | name  | rank | total |
+------+-------+------+-------+
|    1 | name1 |    1 |    70 |
|    4 | name4 |    1 |    70 |
|    2 | name2 |    2 |    60 |
|    3 | name3 |    3 |    55 |
|    5 | name5 |    4 |    40 |
+------+-------+------+-------+

@never did you get that value added in rank field in mysql table? — Lepanto, Mar 04 '15 at 07:21

Ankit Bajpai · Answer 2 · 2015-03-04T07:01:49.263

-1

Try this:-

SELECT #, name, aa, bb, cc, total, FIND_IN_SET( total, (
                                                SELECT GROUP_CONCAT( total ORDER BY total DESC ) 
                                                FROM your_tab
                                                        )
                                               ) AS rank
FROM your_tab;

edited Mar 04 '15 at 07:01

answered Mar 04 '15 at 06:55

Ankit Bajpai

13,128
4
25
40

thats why I ask this question...really – Ikong Mar 04 '15 at 06:59
u just copy paste your solution from others as you can see you forget to change the score – Ikong Mar 04 '15 at 07:00
Okk. I accepted that i hav found this sol on internet, but ultimately i m helping only you. – Ankit Bajpai Mar 04 '15 at 07:01

MySQL Rank my total score

2 Answers2