How to get correct position on ties in mysql rankings

Question

This is my code and works for ties but it does not skip position on ties

SELECT   `item`, (`totalrate` / `nrrates`),
         @rank_count := @rank_count + (totalrate/nrrates < @prev_value) rank,
         @prev_value := totalrate/nrrates avg
FROM     table, (SELECT @prev_value := NULL, @rank_count := 1) init
 ORDER BY avg DESC

Here is the out I get

item        (`totalrate` / `nrrates`)    rank     avg

Virginia        10.0000                    1      10
Ana             9.7500                     2      9.75
Angeie          9.72                       3      9.72
Carel           9.666666666                4      9.66
sammy           9.666666666                4      9.66
Oda             9.500000000                5      9.5

I want

item        (`totalrate` / `nrrates`)    rank     avg

Virginia        10.0000                    1      10
Ana             9.7500                     2      9.75
Angeie          9.72                       3      9.72
Carel           9.666666666                4      9.66
sammy           9.666666666                4      9.66
Oda             9.500000000                6      9.5

To skip the 5 position

I would like to merge with this that does skip position on ties

(I took the below code from this post MySQL Rank in the Case of Ties)

SELECT t1.name, (SELECT COUNT(*) FROM table_1 t2 WHERE t2.score > t1.score) +1
AS rnk
FROM table_1 t1

how would I modify my code to get it to skip position with the above code it looks simple but i haven't figured it out. Thanks

Can you post a set of sample records and an expected output from the select. — Ravinder Reddy, May 30 '14 at 18:39

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

On ties, you may want to skip and use current row num to next unmatched avg value row as next rank.
Following should help you

SELECT   `item`, @curr_avg := ( `totalrate` / `nrrates` )
       , case when @prev_avg = @curr_avg then @rank := @rank 
              else @rank := ( @cur_row + 1 )
          end as rank
       , @cur_row := ( @cur_row + 1 ) as cur_row 
       , @prev_value := @curr_avg avg
FROM     table
       , ( SELECT @prev_avg := 0, @curr_avg := 0
                , @rank := 0, @cur_row := 0 ) init
ORDER BY avg DESC

Similar examples:

edited May 23 '17 at 11:56

Community

1
1

answered May 30 '14 at 19:09

Ravinder Reddy

23,692
6
52
82

I doesnt give me a 1-10 rank – user3692310 May 30 '14 at 20:38
What was the result? Can you post sample records from your table on http://sqlfiddle.com and let us know? – Ravinder Reddy May 31 '14 at 02:38

Joseph B · Answer 2 · 2014-05-30T20:41:45.113

0

Here's another alternative. First, the averages are calculated. If they are already available in a table, it would be even easier (as can be seen in the fiddle demo). Anyways, the rank is based on the logic of counting how many items have a lesser average than the current item.

SELECT
A1.`item`,
A1.avg,
COUNT(A2.`item`) avg_rank
FROM
(
SELECT   `item`, (`totalrate` / `nrrates`),
         @prev_value := totalrate/nrrates avg
FROM     table, (SELECT @prev_value := NULL, @rank_count := 1) init
) A1   --alias for the inline view
INNER JOIN
(
SELECT   `item`, (`totalrate` / `nrrates`),
         @prev_value := totalrate/nrrates avg
FROM     table, (SELECT @prev_value := NULL, @rank_count := 1) init
) A2   --alias for the inline view
ON A2.avg < A1.avg
GROUP BY A1.id, A1.avg
ORDER BY A1.avg;

SQL Fiddle demo

edited May 30 '14 at 20:41

answered May 30 '14 at 19:22

Joseph B

5,519
1
15
19

can you tell me what A1 and A2 mean – user3692310 May 30 '14 at 20:39
A1 and A2 are the aliases that are given to the "inline views". Basically, they are the names which are used to refer to the columns inside the two sub-queries. – Joseph B May 30 '14 at 20:40
I have added comments in the two places where these aliases are defined. Then, they are used in the outer query. – Joseph B May 30 '14 at 20:42
Note that you shouln't use the fiddle out of the box as one of the values is missing. In this case the (10,0). – Dirk Mar 20 '15 at 09:10

How to get correct position on ties in mysql rankings

2 Answers2