Rank users in mysql by their points

Question

I am trying to rank my students by their points that I've calculated before but the problem is if students have same points they both should be in same rank E.g

Student 1 has full points Student 2 has full points

they both have to be rank as 1;

Here an example of my database

the query I am trying to do is (just for select then I can insert the values to my column)

 SELECT  a.points
        count(b.points)+1 as rank
FROM  examresults a left join examresults b on a.points>b.points
group by  a.points;

Edit for being more clear:

Student 1 points 80
Student 2 points 77.5
Student 3 points 77.5
Student 4 points 77

their ranks should be like

Student 1 Rank 1
Student 2 Rank 2
Student 3 Rank 2
Student 4 Rank 3

my current query returns a values like

As it is missing the third rank. (because second rank has 2 values)

let say student A and B has 20 points and C has 18, You want A and B be rank 1 and C rank 2 or 3? — Juan Carlos Oropeza, Jan 06 '16 at 16:32

Juan Carlos Oropeza · Accepted Answer · 2016-01-06T20:19:11.827

2

This is just a fix of Gordon solution using variables. The thing is your rank function isnt the way rank should work. (student 4 should be rank 4)

SQL Fiddle Demo You can add more student to improve the testing.

select er.*,
       (@rank := if(@points = points, 
                    @rank, 
                    if(@points := points,    
                       @rank + 1, 
                       @rank + 1                       
                      )
                   )                  
       ) as ranking
from students er cross join
     (select @rank := 0, @points := -1) params
order by points desc;

OUTPUT

| id | points | ranking |
|----|--------|---------|
|  1 |     80 |       1 |
|  2 |     78 |       2 |
|  3 |     78 |       2 |
|  4 |     77 |       3 |
|  5 |     66 |       4 |
|  6 |     66 |       4 |
|  7 |     66 |       4 |
|  8 |     15 |       5 |

edited Jan 06 '16 at 20:19

answered Jan 06 '16 at 20:11

Juan Carlos Oropeza

47,252
12
78
118

This does a `dense_rank()`, rather than a rank. So, it will return 1, 1, 1, 2, instead of 1, 1, 1, 4. . . . But that seems to be what the OP is asking for. – Gordon Linoff Jan 07 '16 at 14:11
can you please do some modification to your query, if data has multiple rows for a same student and then we need to calculate the rank based on the `sum` of all the points of that user. See this link for data http://sqlfiddle.com/#!9/95dff ? thanks – Sachin Jan 08 '16 at 16:48
@Sachin, Sorry I dont understand, do you work with the OP and need a new requirement? I dont see OP say a student can have more than one point result – Juan Carlos Oropeza Jan 08 '16 at 16:51
@Sachin If you need help with a similar but different problem just create a new question and I will help there just let me know. But I cant change this answer because is related to this question. – Juan Carlos Oropeza Jan 08 '16 at 16:54
@JuanCarlosOropeza, no problem. I've just created a new question, can you please look into that.. http://stackoverflow.com/questions/34682333/rank-users-in-mysql-by-their-total-points-across-multiple-rows – Sachin Jan 08 '16 at 17:04

score 1 · Answer 2 · answered Jan 06 '16 at 16:31

You want a real rank, which is calculated by the ANSI standard rank() function. You can implement this in MySQL using this logic:

select er.*,
       (select 1 + count(*)
        from examresults er2
        where er2.points > er.points
       ) as ranking
from exampleresults er;

For larger tables, you can do this with variables, but it is a rather awkward:

select er.*,
       (@rank := if(@rn := @rn + 1              -- increment row number
                    if(@points = points, @rank, -- do not increment rank
                       if(@points := points,    -- set @points
                          @rn, @rn              -- otherwise use row number
                         )
                       )
                   )
       ) as ranking
from examresults er cross join
     (select @rn := 0, @rank := 0, @points := -1) params
order by points desc;

Gouda Elalfy · Answer 3 · 2016-01-06T19:17:12.353

0

this query achieve what do you want:

SELECT  student_id , points, (select count(distinct(points))+1 as rank 
from examresults internal 
where internal.points > external.points order by points)
FROM  examresults external 
group by  student_id

edited Jan 06 '16 at 19:17

answered Jan 06 '16 at 16:49

Gouda Elalfy

6,888
1
26
38

sorry still not working. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'onlineform_1.external.points' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by if I delete group by part then it messes up totally – Anar Bayramov Jan 06 '16 at 19:34

Rank users in mysql by their points

3 Answers3

Linked

Related