How to create rankings for different columns in MySQL?

Question

I have the following table:

id   type   result
---  ---    ------
1    a       39
1    b       412
2    a       42
3    a       32
3    b       390

Now I would like to add rankings to this table for the result dependent on the type, so every type should have a single ranking ascending according to the value in the column result (and there are around 20 different types in the original table).

The result should be like this:

id   type   result  rank
--  ---    ------   ----
1    a       39     2
1    b       412    2
2    a       42     3
3    a       32     1
3    b       390    1

I found several solutions to add rankings to tables here, but how can I create rankings taking account the value of another column?

possible duplicate of [MySQL - How to do an Oracle RANK() OVER(ORDER BY score DESC)](http://stackoverflow.com/questions/24953173/mysql-how-to-do-an-oracle-rank-overorder-by-score-desc) — Bulat, Sep 02 '14 at 14:59
also http://stackoverflow.com/questions/8394295/within-group-sorts-in-mysql — Bulat, Sep 02 '14 at 15:00

Gordon Linoff · Answer 1 · 2014-09-02T15:02:33.350

The easiest way to do this in MySQL is using variables:

  select t.*,
         @rn := if(@type = type, @rn + 1,
                   if(@type := type, 1, 1)
                  ) as ranking
  from table t cross join
       (select @type := NULL, @rn := 0) vars
  order by type, result;

Note that all the variables are used in a single expression in the select clause. This is important because MySQL does not guarantee the order of evaluation of expressions. Hence, the if(@type := type, 1, 1)) piece.

How to create rankings for different columns in MySQL?

1 Answers1