Why local variable doesn't save value?

Question

I'm writing query in mysql5.7 to emulate dense_rank(). I have problem with scope of variable.

I've tried using variable in diffrent ways, but always Place in rank is wrong counted. It seems that variable @total i wrong set.

SET @row_number = 1;
SET @total =null;

SELECT
    CONCAT(`u`.`name`, ' ' ,`u`.`surname`) as `User`,
    SUM(`oi`.quantity * `oi`.price)  as `Total amount`,
    CASE
        WHEN @total = SUM(`oi`.quantity * `oi`.price) THEN
            @row_number
        ELSE
            @row_number:= @row_number + 1
        END
    as `Place in rank`,
    @total := SUM(`oi`.quantity * `oi`.price)
FROM `user` u
    LEFT JOIN `order` o ON `u`.`user_id`=`o`.user_id
    LEFT JOIN `order_item` oi ON `oi`.`order_id`=`o`.order_id
WHERE `o`.`date` > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY user
ORDER BY `Total amount` DESC

Here's my fiddle https://www.db-fiddle.com/f/5yiyp6Zyt2eB5h26RT5Lmf/10

Column place in rank has actual values 4,3,2 but I

expect 1,1,2

Answer 1

In this case SELECT is evaluated before ORDER BY, because no index can be used for the the ORDER BY clause. And the columns could be evaluated in any order (especially when aggregate functions are involved). SQL is not a procedural language. You can though try to "force" the execution/evaluation order. In this case you need (at least) to wrap your query into an ordered subquery. Also - The @row_number should be initialized as 0:

SET @row_number = 0;
SET @total = null;

SELECT *,
  CASE WHEN @total = `Total amount`
    THEN @row_number
    ELSE @row_number:= @row_number + 1
  END AS `Place in rank`,
  @total := `Total amount`
FROM (
  SELECT
    CONCAT(`u`.`name`, ' ' ,`u`.`surname`) as `User`,
    SUM(`oi`.quantity * `oi`.price)  as `Total amount`
  FROM `user` u
  LEFT JOIN `order` o ON `u`.`user_id`=`o`.user_id
  LEFT JOIN `order_item` oi ON `oi`.`order_id`=`o`.order_id
  WHERE `o`.`date` > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
  GROUP BY user
  ORDER BY `Total amount` DESC
) x

db-fiddle

This might work now. But you never know, when it doesn't. Consider to solve such tasks in a procedural language, or upgrade to a version with window functions.

However - If forced to use "SQL", I would write the query a bit differently:

SELECT x.*,
  CASE WHEN @total = `Total amount`
    THEN @row_number
    ELSE @row_number:= @row_number + 1 + 0*(@total := `Total amount`)
  END AS `Place in rank`
FROM (
  SELECT
    CONCAT(`u`.`name`, ' ' ,`u`.`surname`) as `User`,
    SUM(`oi`.quantity * `oi`.price)  as `Total amount`
  FROM `user` u
  LEFT JOIN `order` o ON `u`.`user_id`=`o`.user_id
  LEFT JOIN `order_item` oi ON `oi`.`order_id`=`o`.order_id
  WHERE `o`.`date` > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
  GROUP BY user
  ORDER BY `Total amount` DESC
) x
CROSS JOIN (SELECT @row_number := 0, @total := null) init_vars

db-fiddle