0
158
VisualizaçõesSelect order with row counter not working as expected
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:
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:
1 Respostas
Responde à pergunta0
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;