CODEWITHSUNDEEP
mysqlsql
spraff
spraff

Reputation: 33395

MySQL rank calculation is failing, variable always null

I want to maintain the stored rank index of each row in a MySQL table based on the value of a rating column in the same row. I found the solution here and here but it doesn't work.

SELECT * FROM `MyTable` T
JOIN
(
    SELECT `id`, @rownum=@rownum+1 AS `rank_calculated`
    FROM `MyTable`, (SELECT @rownum=0) AS `inline_trick`
    ORDER BY `rating`
) R
ON R.`id` = T.`id`

This selects all rows in MyTable but the rank_calculated column is always null. Can anyone tell me why?

mysql Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (i686) using readline 6.2

Upvotes: 0

Views: 172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Try using :=:

SELECT *
FROM `MyTable` T JOIN
     (SELECT `id`, @rownum:=@rownum+1 AS `rank_calculated`
      FROM `MyTable`, (SELECT @rownum:=0) AS `inline_trick`
      ORDER BY `rating`
    ) R
    ON R.`id` = T.`id`;

EDIT:

Just to be clear, within a select statement = refers to boolean comparison, not assignment. You need := for an assignment. You can read more here.

Upvotes: 1

Related Questions