mysql grouped ranking with ties

Question

so i have data in a table like this:

id        total   group_id
1897      738      1
2489      716      2
2325      715      3
1788      702      2
1707      699      3
2400      688      3
2668      682      2
1373      666      1
1494      666      1
1564      660      1
2699      659      1
1307      648      4
1720      645      4
2176      644      1
1454      644      4
2385      639      3
1001      634      2
2099      634      4
1006      632      1
2587      630      3
1955      624      3
1827      624      4
2505      623      4
2062      621      3
1003      618      1
2286      615      4
2722      609      4

how can i rank the ids per group based on the total and giving the same rank when there is a tie?

i have tried this solution below but it doesnt take care of the ties.

SELECT g1.admission_no
 , g1.total
 , g1.stream_id
 , COUNT(*) AS rn
 FROM ranktest   AS g1
   JOIN ranktest   AS g2
   ON (g2.total, g2.admission_no) >= (g1.total, g1.admission_no)
   AND g1.stream_id = g2.stream_id
   GROUP BY g1.admission_no
    , g1.stream_id
    , g1.total
   ORDER BY g1.stream_id
    , total ;

expected

id        total   group_id rank
1897      738      1        1
2489      716      2        1
2325      715      3        1
1788      702      2        2 
1707      699      3        2
2400      688      3        3
2668      682      2        3
1373      666      1        2
1494      666      1        2
1564      660      1        3
2699      659      1        4
1307      648      4        1
1720      645      4        2
2176      644      1        4
1454      644      4        3
2385      639      3        4
1001      634      2        4
2099      634      4        4
1006      632      1        5
2587      630      3        5
1955      624      3        6
1827      624      4        5
2505      623      4        6
2062      621      3        6
1003      618      1        6
2286      615      4        7 
2722      609      4        8

[select](http://dev.mysql.com/doc/refman/5.7/en/select.html) + [aggregates](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html). We don't write code for you. You write code, we (maybe) try fix it. — Marc B, Jul 22 '16 at 15:14
@MarcB i have this code using joins but id doesnt take care of ties and i dont know how to fix it — GOA, Jul 22 '16 at 15:19
so show this code. but if you want ties, you'll need extra query scaffolding to detect ties and pull up the records that produced those ties. — Marc B, Jul 22 '16 at 15:23
You need grouping and rank `variables`, and safe use not hacks, see Baron's [Obligatory Reading](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/). By the way this site already contains examples of this. — Drew, Jul 22 '16 at 15:29

score 2 · Answer 1 · answered Jul 22 '16 at 16:15

If original order is not very important you can start from:

http://sqlfiddle.com/#!9/a15a2/10

SELECT
    ranktest.*,
    IF(@rank IS NULL,@rank:=1, IF(@prev!=group_id,@rank:=1,@rank:=@rank+1) ) rank,
     @prev:=group_id
FROM ranktest
ORDER BY group_id, total

but keep in mind that this is not very efficient query from performance perspective.

Drew · Answer 2 · 2016-07-22T16:47:07.863

From the MySQL Manual Page entitled User-Defined Variables:

In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.

This is why so few people write these answers correctly and safely. The object is not to hit the desired results once, but to do so again and again in real-world environments using best practices and guaranteed results.

If you don't read the Obligatory Doc, and implement it, your results are not guaranteed. There is no lazy way to do this and one shouldn't even bother :p

select id,total,group_id,rank 
from 
(   select id,total,group_id, 
    @rn:=if(  group_id!=@curr_grp, greatest(@grp_rank:=1,0), 
        if(total=@prev_grp_total,@grp_rank,greatest(@grp_rank:=@grp_rank+1,0) ) ) as rank, 
    @curr_grp:=group_id, 
    @prev_grp_total:=total 
    from trans01 
    cross join (select @grp_rank:=0,@curr_grp:=0,@prev_grp_total:=-1) xParams 
    order by group_id,total desc 
)xDerived; 

+------+-------+----------+------+
| id   | total | group_id | rank |
+------+-------+----------+------+
| 1897 |   738 |        1 | 1    |
| 1373 |   666 |        1 | 2    |
| 1494 |   666 |        1 | 2    |
| 1564 |   660 |        1 | 3    |
| 2699 |   659 |        1 | 4    |
| 2176 |   644 |        1 | 5    |
| 1006 |   632 |        1 | 6    |
| 1003 |   618 |        1 | 7    |

| 2489 |   716 |        2 | 1    |
| 1788 |   702 |        2 | 2    |
| 2668 |   682 |        2 | 3    |
| 1001 |   634 |        2 | 4    |

| 2325 |   715 |        3 | 1    |
| 1707 |   699 |        3 | 2    |
| 2400 |   688 |        3 | 3    |
| 2385 |   639 |        3 | 4    |
| 2587 |   630 |        3 | 5    |
| 1955 |   624 |        3 | 6    |
| 2062 |   621 |        3 | 7    |

| 1307 |   648 |        4 | 1    |
| 1720 |   645 |        4 | 2    |
| 1454 |   644 |        4 | 3    |
| 2099 |   634 |        4 | 4    |
| 1827 |   624 |        4 | 5    |
| 2505 |   623 |        4 | 6    |
| 2286 |   615 |        4 | 7    |
| 2722 |   609 |        4 | 8    |
+------+-------+----------+------+

The trick is in setting the var embedded in the `least()`, `greatest()`, or `coalesce()` . It may look ridiculous, saying, give me the greatest of 9 or 0, but there is a method to the madness. — Drew, Jul 22 '16 at 18:37

score 0 · Answer 3 · answered Jul 22 '16 at 16:12

came up with this answer after googling a bit..not sure is its the best but it works for my case:

 SELECT id, group_id, total,
   @std:=CASE WHEN @grp <> group_id THEN  concat(left(@grp:=group_id, 0), 1) ELSE if(@prev=total,@std,@std+1) END AS rn,@prev:=total 
FROM
    (SELECT @std:= -1) s,
 (SELECT @grp:= -1,@prev:=null) c,
 (SELECT *
  FROM table
  ORDER BY group_id, total desc
) s

mysql grouped ranking with ties

3 Answers3