Rank function in MySQL assign a rank

Question

I need to assign a rank to TC5 not the id.

SELECT a.id, a.user_id, a.tc1, a.tc4, min(a.tc5), a.tc2, b.avatar, c.username, @curRank := @curRank + 1 AS Rank
FROM 
treningove_casy a INNER JOIN  
sn_users b ON a.user_id=b.id INNER JOIN
users c ON a.user_id=c.id , (SELECT @curRank := 0) r
WHERE  a.tc2 LIKE  'Motokáry Modrice' 
GROUP BY a.user_id

So how is the picture. Please help

The modified code

SELECT x.*, (@curRank := @curRank + 1) as Rank
FROM (SELECT a.id, a.user_id, a.tc1, a.tc4, min(a.tc5) as tc5,
             a.tc2, b.avatar, c.username, 
      FROM sbhgl_chronoengine_chronoforms_datatable_treningove_casy a INNER JOIN  
           sbhgl_jsn_users b
           ON a.user_id = b.id INNER JOIN
           sbhgl_users c
           ON a.user_id = c.id 
      WHERE  a.tc2 LIKE  'Motokáry Modřice' 
      GROUP BY a.user_id
     ) x CROSS JOIN
     (SELECT @curRank := 0) params
ORDER BY tc5 DESC;

Answer 1

SELECT a.id, a.user_id, a.tc1, a.tc4, a.tc2, b.avatar, c.username, 
    (select rank from (SELECT 
        IF (@score=s.tc5, @rank:=@rank, @rank:=@rank+1) rank,
        @score:=s.tc5 tc5s
        FROM treningove_casy s,
        (SELECT @score:=0, @rank:=0) r
        ORDER BY tc5 DESC) s ) as rank
FROM 
treningove_casy a INNER JOIN  
sn_users b ON a.user_id=b.id INNER JOIN
users c ON a.user_id=c.id , (SELECT @curRank := 0) r
WHERE  a.tc2 LIKE  'Motokáry Modrice' 
GROUP BY a.user_id

You can try above solution, Hope this will help you.

Answer 2

In MySQL, you typically use variables for ranks. In your case, you would use a subquery. I find that the variable method doesn't always work with GROUP BY:

SELECT x.*, (@curRank := @curRank + 1) as Rank
FROM (SELECT a.id, a.user_id, a.tc1, a.tc4, min(a.tc5) as tc5,
             a.tc2, b.avatar, c.username, 
      FROM treningove_casy a INNER JOIN  
           sn_users b
           ON a.user_id = b.id INNER JOIN
           users c
           ON a.user_id = c.id 
      WHERE  a.tc2 LIKE  'Motokáry Modrice' 
      GROUP BY a.user_id
     ) x CROSS JOIN
     (SELECT @curRank := 0) params
ORDER BY tc5 DESC;

Note: The use of table aliases is good. It is much easier to understand a query, though, if the aliases are abbreviations for the table names.