user1267980 user1267980 - 6 months ago 42
MySQL Question

Ranking tied results in MYSQL

I have a MYSQL table which looks something like this:

ID | NAME CODE | SCORE
1: A01: 1
2: A01: 4
3: A01: 5
4: A02: 2
5: A02: 3
6: A02: 3
7: A02: 7


And the ranking outcome I am looking for is this (taking into account the 2 different NameCode groups):

ID | NAME CODE | SCORE | RANK

1: A01: 1: 1
2: A01: 4: 2
3: A01: 5: 3
4: A02: 2: 1
5: A02: 3: 2
6: A02: 3: 2
7: A02: 7: 4


I'm able to rank scores using the following code:

SELECT
my_table.id,
my_table.NameCode,
my_table.Score,
@prev := @curr,
@curr := Score,
@rank := IF(@prev = @curr, @rank, @rank + @i) AS rank,
IF(@prev <> Score, @i:=1, @i:=@i+1) AS counter
FROM
my_table,
(SELECT @curr := null, @prev := null, @rank := 1, @i := 0
) tmp_tbl


ORDER BY
my_table.Score ASC

However this does not rank according to each NameCode group. It gives the following result:

ID | NAME CODE | SCORE | RANK

1: A01: 1: 1
2: A01: 4: 5
3: A01: 5: 6
4: A02: 2: 2
5: A02: 3: 3
6: A02: 3: 3
7: A02: 7: 7


Does anyone know the most efficient way I can rank within each NameCode group? Thanks.

Answer

basically you want your logic to say if they are both the same repeat rank, if the name is the same then increment rank or else reset it back to 1

SELECT 
    id, 
    name, 
    score,
    @rank := if(
        @name = name and @score = score, 
        @rank, 
        if(@name = name, @rank + 1, 1)
    ),
    @name := name, @score:= score
FROM your_table
CROSS JOIN (SELECT @rank := 1, @name := '', @score := 0) t;

FIDDLE

EDIT: if you need the data sorted just do this

SELECT 
    id, 
    name, 
    score,
    @rank := if(
        @name = name and @score = score, 
        @rank, 
        if(@name = name, @rank + 1, 1)
    ),
    @name := name, @score:= score
FROM (
    SELECT * FROM your_table
    ORDER BY id, score
) tt
CROSS JOIN (SELECT @rank := 1, @name := '', @score := 0) t;