How to create a MySQL 5.6 Rank with JOIN and multiple sorting criteria

I am stuck trying to return the rank of an SQL query.

SELECT c.id, c.score, i.sheetscore, @curRank := @curRank + 1 AS rank
FROM chart c LEFT JOIN indicator as i
ON c.indicator_id = i.id, (
SELECT @curRank :=0
) q
ORDER BY c.score DESC, i.sheetscore DESC
;

The final row should show the rank of the sorting that is presented correctly. I should be seeing Rank = 1,2,3 but I get this instead … I’ve tried numerous variations of the SQL statement but I cannot strike on the solution.

'ID','SCORE','SHEETSCORE', 'RANK'
'11767', '1', '0.7325', '11767'
'11765', '1', '0.7325', '11765'
'8365', '1', '0.6925', '8365'
'8363', '1', '0.6925', '8363'
'8615', '1', '0.6875', '8615'
'8617', '1', '0.6875', '8617'
'11646', '1', '0.685455', '11646'
'11647', '1', '0.685455', '11647'

Ideally, I would use this query to:

SELECT RANK from Chart where ID= 11646  ## as an example

Answer

I would recommend joining and ordering in a subquery first, then computing the rank. Also, you should not mix implicit and explicit joins – matter of fact, always use explicit joins:

SELECT x.*, @curRank := @curRank + 1 AS rank
FROM (
    SELECT c.id, c.score, i.sheetscore
    FROM chart c 
    LEFT JOIN indicator i ON c.indicator_id = i.id
    ORDER BY c.score DESC, i.sheetscore DESC
) x
CROSS JOIN (SELECT @curRank :=0) q
ORDER BY score DESC, sheetscore DESC

Note that, if you are running MySQL 8.0, this is straight-forward with row_number():

SELECT 
    c.id, 
    c.score, 
    i.sheetscore, 
    ROW_NUMBER() OVER(ORDER BY c.score DESC, i.sheetscore DESC) rn
FROM chart c 
LEFT JOIN indicator i ON c.indicator_id = i.id
ORDER BY c.score DESC, i.sheetscore DESC