I'm trying to get all total points per user, sort them in descending order and print their ranks.My query is working almost fine, but it does not set user's rank properly.
SET @rank := 0;
SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC
User schema:
|---------------------|------------------|
| id | username |
|---------------------|------------------|
| 1 | test1 |
|---------------------|------------------|
| 2 | test2 |
|---------------------|------------------|
| 3 | test3 |
|---------------------|------------------|
| 8 | test4 |
|---------------------|------------------|
Solution schema:
|---------------------|------------------|-----------------|
| id | author_id | score |
|---------------------|------------------|-----------------|
| 1 | 1 | 55 |
|---------------------|------------------|-----------------|
| 2 | 2 | 5 |
|---------------------|------------------|-----------------|
| 3 | 3 | 22 |
|---------------------|------------------|-----------------|
| 4 | 8 | 43 |
|---------------------|------------------|-----------------|
| 5 | 8 | 43 |
|---------------------|------------------|-----------------|
The result is:
|---------------------|------------------|-----------------|
| rank | username | totalScore |
|---------------------|------------------|-----------------|
| 4 | test4 | 86 |
|---------------------|------------------|-----------------|
| 1 | test1 | 55 |
|---------------------|------------------|-----------------|
| 3 | test3 | 22 |
|---------------------|------------------|-----------------|
| 2 | test2 | 5 |
|---------------------|------------------|-----------------|
Why does that happen?
The expected result should be:
|---------------------|------------------|-----------------|
| rank | username | totalScore |
|---------------------|------------------|-----------------|
| 1 | test4 | 86 |
|---------------------|------------------|-----------------|
| 2 | test1 | 55 |
|---------------------|------------------|-----------------|
| 3 | test3 | 22 |
|---------------------|------------------|-----------------|
| 4 | test2 | 5 |
|---------------------|------------------|-----------------|
As i said in the comment. You have first sum up the score to rank it.
SELECT
(@rank:=@rank + 1) rank, username, totalscore
FROM
(SELECT
u.username, SUM(s.score) AS totalScore
FROM
solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC) t1,
(SELECT @rank:=0) r1
See example the second query is mine
Schema (MySQL v5.7)
CREATE TABLE users (
`id` INTEGER,
`username` VARCHAR(5)
);
INSERT INTO users
(`id`, `username`)
VALUES
('1', 'test1'),
('2', 'test2'),
('3', 'test3'),
('8', 'test4');
CREATE TABLE solution (
`id` INTEGER,
`author_id` INTEGER,
`score` INTEGER
);
INSERT INTO solution
(`id`, `author_id`, `score`)
VALUES
('1', '1', '55'),
('2', '2', '5'),
('3', '3', '22'),
('4', '8', '43'),
('5', '8', '43');
Query #1
SET @rank := 0;
There are no results to be displayed.
Query #2
SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC;
| rank | totalScore | username |
| ---- | ---------- | -------- |
| 4 | 86 | test4 |
| 1 | 55 | test1 |
| 3 | 22 | test3 |
| 2 | 5 | test2 |
Query #3
SELECT
(@rank := @rank +1) rank
,username
,totalscore
FROM
(SELECT
u.username,
SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC) t1,(SELECT @rank := 0) r1;
| rank | username | totalscore |
| ---- | -------- | ---------- |
| 1 | test4 | 86 |
| 2 | test1 | 55 |
| 3 | test3 | 22 |
| 4 | test2 | 5 |