Select order with row counter not working as expected

user_2163927 0
Viewed 49

My rank order is not correct, where in this query is the order not matching the rank?

SELECT @r := @r+1 as rank, 
        z.* 
 FROM(
         SELECT 
             u.user_id,
             u.score,
             c.username
             FROM `e_highscores` u
     
             JOIN (
                 SELECT  cc.user_id, cc.username
                 FROM e_users as cc
                 ) as c
                 ON  u.user_id = c.user_id
     ORDER BY  u.score DESC
 )z, 
 (SELECT @r:=0)y;

Output:

enter image description here

Switching the order by to:

        SELECT @r := @r+1 as rank, 
        z.* 
 FROM(
         SELECT 
             u.user_id,
             u.score,
             c.username
             FROM `e_highscores` u
     
             JOIN (
                 SELECT  cc.user_id, cc.username
                 FROM e_users as cc
                 ) as c
                ON  u.user_id = c.user_id
       
 )z, 
 (SELECT @r:=0)y
  ORDER BY  z.score DESC

Produces:

enter image description here

mariadb mysql
1 Answers

I create a table in my sandbox (mysql 5.7.31), and it seems that your query could get correct result:

CREATE TABLE `e_highscores` (
  `user_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE `e_users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

insert into `e_highscores` (user_id, score) values (2, 200), (15, 300), (11,121);
insert into `e_users` (user_id, username) values (2, 'Pie'), (15, 'Adam'), (11, 'Hershey');

SELECT @r := @r+1 as urank,
    z.*
FROM(
 SELECT
     u.user_id,
     u.score,
     c.username
     FROM `e_highscores` u

     JOIN e_users as c
     ON  u.user_id = c.user_id

) z,
(SELECT @r:=0) y
ORDER BY  z.score DESC;

The result is:

1   15  300 Adam
2   2   200 Pie
3   11  121 Hershey

I guess your issue may be caused by 'rank' keyword in mysql?? Could you let me know your mysql version, please?

mariadb is different with mysql when using user defined variable with order by together.

Beware order of evaluation of rows when sorting. It would appear (at least as far as I've tested it) that when statements of the form:

SELECT @var:= FROM...

are executed, @var will always correspond to the value in the last row returned, as you might expect.

But if you do something like:

SELECT DISTINCT IFNULL(@var:=Name,'unknown') FROM Customers ORDER BY LIMIT 10

The statement containing the variable assignment will be executed before the order-by. In this case, the value left in @var at the end might not even correspond to any of the rows returned!

Please refer to mariadb offical doc for more details.

To generate correct result, please use the following query:

select 
  urank, t.user_id, t.score, c.username 
from (
  select 
    @row := @row + 1 as urank, 
    u.user_id, 
    u.score 
  from e_highscores u 
  order by score desc
) t join e_users as c on t.user_id = c.user_id 
order by score desc;
user_138955720
Related