0

I have very much similar kind of requirement as described in this question.

Rank users in mysql by their points

The only difference is in my data. The above problem has the data where table has only row per student. But in my case there may be a possibility that table contains multiple rows for a single student like this

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

So now rank should be calculated based on the SUM of points (total) that user has. So in this case result would be.

  • Student 2 Rank 1 with 82 points
  • Student 1 Rank 2 with 80 points
  • Student 3 Rank 3 with 77 points
  • Student 4 Rank 3 with 77 points

SQL Fiddle for data

I tried couple of things with the solution of above question but couldn't get the result. Any help would be appreciated.

Community
  • 1
  • 1
Sachin
  • 40,216
  • 7
  • 90
  • 102
  • Use the solution from that table, but instead of the original table, rank the results of a subquery that calculates `SUM(points) ... `GROUP BY student` – Barmar Jan 08 '16 at 17:08
  • Please show at least one of the things you tried, so we can help you learn from your mistake. We're not going to write it for you. – Barmar Jan 08 '16 at 17:09

3 Answers3

4

Using the same query in my previous answer just change the table student for a subquery to combine all records of every student

change [student er]  for 

(SELECT `id`, SUM(`points`) as `points`
 FROM students 
 GROUP BY `id`) er

SQL DEMO

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

OUTPUT 

| id | points | ranking |
|----|--------|---------|
|  5 |     91 |       1 |
|  6 |     81 |       2 |
|  1 |     80 |       3 |
|  2 |     78 |       4 |
|  3 |     78 |       4 |
|  4 |     77 |       5 |
|  7 |     66 |       6 |
|  8 |     15 |       7 |
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • I read you have some problem with mySQL version vs sqlFiddle, hope this work for you. – Juan Carlos Oropeza Jan 08 '16 at 19:10
  • @Sachin @ JuanCarlosOropeza I Upvoted this answer because I think that double IF is a great idea but ranking result is wrong. Your ranking is 1-2-3-4-4-5 but the correct ranking is 1-2-3-4-4-6. My code gives the correct ranking. – genespos Jan 11 '16 at 14:30
  • No, @genespos, I think ranking 1-2-3-4-4-5 is fine and that's what I was looking for. In your result Rank 5 is missing as you are saying.. – Sachin Jan 11 '16 at 16:03
  • @Sachin In the olimpic games, if the 1st competitor have 100 pts, two competitors have 90 pts and another 80 pts: the 1st gets gold the two who have 90 pts get silver but the one who has 80 pts doesn't get bronze because he's 4th. But if this is good for you : No problem. ;) – genespos Jan 11 '16 at 16:32
  • @genespos This is called `dense_rank`, you can see the diference here. http://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle – Juan Carlos Oropeza Jan 11 '16 at 17:08
  • @JuanCarlosOropeza Thanks for your hint: I learnt here this difference – genespos Jan 11 '16 at 17:19
3

Try this:

select id, points, @row := ifnull(@row, 0) + diff rank
from (select *, ifnull(@prev, 0) != points diff, @prev := points
      from (select id, sum(points) points
            from students
            group by 1
            order by 2 desc) x) y

See SQLFiddle

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    You're not displaying the rank. – Barmar Jan 08 '16 at 17:08
  • @Sachin: Whilst you can have MySQL include the rank using user variables (as Bohemian has just shown in his edit), it's typically easier to add a rank counter at the application level as you fetch each record from the resultset. – eggyal Jan 08 '16 at 17:14
  • This work almost fine but issue comes when two student has the equal points. look at student 2 and 3 with 78 points each. They both should have rank as `4` instead of `4` and `5`. http://sqlfiddle.com/#!9/95dff/15 – Sachin Jan 08 '16 at 17:26
  • @sachin yes - I've just been working on that - try latest edit – Bohemian Jan 08 '16 at 17:36
  • Its kind of weird thing but It works on SqlFiddle but don't on my PhpMyAdmin with MySql 5.6.22. – Sachin Jan 08 '16 at 18:29
1

EDITED: (This should work)

SELECT I.Id, I.Points, Rk.Rank
FROM
(SELECT Id, Points, @Rk := @Rk+1 As Rank
FROM (SELECT id, SUM(points) AS Points
      FROM students
      GROUP BY id
      ORDER BY Points DESC) As T,
      (SELECT @Rk := 0) AS Rk) As I
INNER JOIN
(SELECT * 
FROM (
    SELECT Id, Points, @Rk2 := @Rk2+1 As Rank
    FROM (SELECT id, SUM(points) AS Points
          FROM students
          GROUP BY id
          ORDER BY Points DESC) As T1,
          (SELECT @Rk2 := 0) AS Rk) AS T2
GROUP BY Points) As Rk
USING(Points)

The output will be:

| Id | Points |   Rank  |
|----|--------|---------|
|  5 |     91 |       1 |
|  6 |     81 |       2 |
|  1 |     80 |       3 |
|  2 |     78 |       4 |
|  3 |     78 |       4 |
|  4 |     77 |       6 |
|  7 |     66 |       7 |
|  8 |     15 |       8 |

After two Ids in 4th position you'll get the 6th position because 5 Ids are before of the 6th.

genespos
  • 3,211
  • 6
  • 38
  • 70