Ranking position without duplicates

user_9986025 0
Viewed 2916

I have an issue with a query, this is my old question that was previously solved mysql get ranking position grouped with joined table

The problem is that when two players have the same score, the query returns the same ranking position, like 1-1-2-3 ecc. How can I fix this? In the player's table there are also player_tickets (that is the number of game played) and player_date that is a timestamp.

I thought to get my ranking on base of player_score first, player_tickets then, and finally player_date

This is my older query

SELECT *, 
       (SELECT 1 + Count(*) 
        FROM   players p2 
        WHERE  p2.`player_game` = p.`player_game` 
               AND p2.player_score > p.player_score  
               AND p2.player_status = 0) AS ranking 
FROM   players p 
ORDER  BY `player_game`, 
          player_score DESC
subquery window-functions sql-order-by mysql sql
3 Answers

You can simply add more columns to the order by of the window function:

rank() over (
    partition by player_game_id 
    order by player_score desc, player_tickets desc, player_date
) as rank

If you really want to avoid having the same rank twice, you can also use row_number(), which guarantees this - when there are ties, row_number() affects distinct numbers (whose order is hence undefined).

user_106767160

Just add the ranking criteria to your WHERE clause:

SELECT *, 
  (
    SELECT 1 + COUNT(*)
    FROM   players p2 
    WHERE  p2.player_game = p.player_game 
    AND
    (
      (p2.player_score > p.player_score) OR
      (p2.player_score = p.player_score AND p2.player_tickets > p.player_tickets) OR
      (p2.player_score = p.player_score AND p2.player_tickets = p.player_tickets AND p2.player_date > p.player_date) OR
      (p2.player_score = p.player_score AND p2.player_tickets = p.player_tickets AND p2.player_date = p.player_date AND p2.player_id > p.player_id)
    )
  ) AS ranking 
FROM players p 
ORDER BY player_game, player_score DESC;
user_22707620

You can add additional comparisons in the subquery. Or more easily, use variables:

select p.*,
       @rn := if(@g = player_game, @rn + 1,
                 if(@g := player_game, 1, 1)
                ) as ranking
from (select p.*
      from players p
      order by player_game, player_score desc, player_tickets desc, player_date desc
     ) p cross join
     (select @rn := 0, @g := 0) as seqnum;

In newer versions, you would just use row_number() if you don't want ties.

user_11440350
Related