Mysql rank Function

Question

I know there is no rank function in mysql, but I am not able to implement a wokaround of rank function in mysql. Can anyone come to a rescue?

The following is what I want to achieve.

ID  Type    Priority    Expected Rank
1   1       1           1
1   1       2           2
1   2       1           1
1   2       2           2
2   1       1           1
2   2       1           1
3   1       1           1
3   1       2           2
3   2       1           1
3   2       2           2
4   1       1           1
4   2       1           1

For each ID and type, I want to rank by Priority

Thanks in advance.

Answer 1

This is what you can do

select id,type,priority,rank 
from (
   select t.*, 
   @rank := if(
               @prev_id = id and 
               @prev_type = type 
              and @prev_priority !=  priority,@rank+1,1
    ) as rank, 
    @prev_id := id,
    @prev_type:= type, 
    @prev_priority := priority 
    from test t,
    (select @rank:=1,@prev_type:=0,@prev_priority:=0,@prev_id:=0)r 
      order by  id,type,priority
)s ;

Change the table name to real table-name from test

Here is the test case

mysql> insert into test values (1,1,1),(1,1,2),(1,2,1),(1,2,2),(2,1,1),(2,2,1),(3,1,1),(3,1,2),(3,2,1),(3,2,2);
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+------+------+----------+
| id   | type | priority |
+------+------+----------+
|    1 |    1 |        1 |
|    1 |    1 |        2 |
|    1 |    2 |        1 |
|    1 |    2 |        2 |
|    2 |    1 |        1 |
|    2 |    2 |        1 |
|    3 |    1 |        1 |
|    3 |    1 |        2 |
|    3 |    2 |        1 |
|    3 |    2 |        2 |
+------+------+----------+
10 rows in set (0.00 sec)


mysql> select id,type,priority,rank from (  select t.*, @rank := if(@prev_id = id and @prev_type = type and @prev_priority != priority,@rank+1,1) as rank, @prev_id := id,@prev_type:= type, @prev_priority := priority from test t,(select @rank:=1,@prev_type:=0,@prev_priority:=0,@prev_id:=0)r order by id,type,priority)s ;
+------+------+----------+------+
| id   | type | priority | rank |
+------+------+----------+------+
|    1 |    1 |        1 |    1 |
|    1 |    1 |        2 |    2 |
|    1 |    2 |        1 |    1 |
|    1 |    2 |        2 |    2 |
|    2 |    1 |        1 |    1 |
|    2 |    2 |        1 |    1 |
|    3 |    1 |        1 |    1 |
|    3 |    1 |        2 |    2 |
|    3 |    2 |        1 |    1 |
|    3 |    2 |        2 |    2 |
+------+------+----------+------+
10 rows in set (0.00 sec)