mysql group ranking with ties

Question

I found this Q&A here at stackOverflow while searching for a way to group rank data. The solutions work perfectly but how does one account for ties within the groups?

So far I know how to rank, but I don't know how to account for the ties in groups.

+--------------------------------------------+
|   id         |   code       | score | rank |
+--------------------------------------------+ 
| 2477         |          312 |    64 |    1 |
| 1865         |          312 |    63 |    2 |
| 2511         |          312 |    62 |    3 |
| 2890         |          312 |    61 |    4 |
| 1335         |          312 |    61 |    5 |
| 1504         |          312 |    60 |    6 |
| 1385         |          312 |    60 |    7 |
| 1984         |          312 |    59 |    8 |
| 2477         |          212 |    64 |    1 |
| 1865         |          212 |    63 |    2 |
| 2511         |          212 |    62 |    3 |
| 2890         |          212 |    61 |    4 |
| 1335         |          212 |    61 |    5 |
| 1504         |          212 |    60 |    6 |
| 1385         |          212 |    60 |    7 |
| 1984         |          212 |    59 |    8 |

What I need is this:

+--------------------------------------------+
|   id         |   code       | score | rank |
+--------------------------------------------+ 
| 2477         |          312 |    64 |    1 |
| 1865         |          312 |    63 |    2 |
| 2511         |          312 |    62 |    3 |
| 2890         |          312 |    61 |    4 |
| 1335         |          312 |    61 |    4 |
| 1504         |          312 |    60 |    5 |
| 1385         |          312 |    60 |    5 |
| 1984         |          312 |    59 |    6 |
| 2477         |          212 |    64 |    1 |
| 1865         |          212 |    63 |    2 |
| 2511         |          212 |    62 |    3 |
| 2890         |          212 |    61 |    4 |
| 1335         |          212 |    61 |    4 |
| 1504         |          212 |    60 |    5 |
| 1385         |          212 |    60 |    5 |
| 1984         |          212 |    59 |    6 |

is there a way to get the above results?

combine your select with my counting of rating :) http://sqlfiddle.com/#!9/e34de/1 — splash58, Aug 02 '15 at 14:10

M Khalid Junaid · Accepted Answer · 2015-08-02T15:25:28.010

If you need to assign same rank for same score with in same code group then you have to nest case statement

select `id`, `code`, `score`,rank
from (
select `id`, `code`, `score`,
@r:= case when @g = `code` 
     then 
         case when @s <> `score` then @r + 1 else @r end 
      else  1
       end rank,
@g:= `code`,
@s:= `score`
from table1 
cross join(select @g:=null,@r:=0,@s:=null) t
order by `code` desc, `score` desc
) t1

First case statement will check for same code group if there is same code found then in then part of parent case there will be another child case statement which will check if the score is different from the previous one then increment rank variable else assign same rank to the current row

DEMO

@M Khalid Junaid.. your fiddle takes care of ties but ranking Is continuing throughout the groups — GOA, Aug 02 '15 at 14:12

mysql group ranking with ties

1 Answers1