0

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

Community
  • 1
  • 1
spraff
  • 32,570
  • 22
  • 121
  • 229

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786