Reputation: 547
My table:
USERID ------ SCORE
1999 -------- 100
1999 -------- 230
2000 -------- 210
I have many USERID
and I need to GROUP BY USERID
to SUM all scores of each USERID
- and make the final Ranking.
I need this:
USERID ----- SCORE ---- USERRANK
1999 ------ 330 ------ 1
2000 ------ 210 ------ 2
WITH THIS:
SELECT USERID, SUM(SCORE), (@rownum := @rownum + 1) UserRank
FROM RESPOSTAS GROUP BY USERID, (SELECT @rownum := 0)
ORDER BY SUM(SCORE) DESC
I Have:
USERID ----- SCORE ---- USERRANK
1999 ------ 330 ------ 1
2000 ------ 210 ------ 1
ALL UserID has the same UserRank..
What is wrong?
Upvotes: 2
Views: 85
Reputation: 30849
You need to use (SELECT @rownum := 0)
in FROM clause and wrap the query inside the outer query, e.g.:
SELECT user.userid, user.rank, (@rownum := @rownum + 1) as rank
from (
SELECT USERID, SUM(rank) as rank
FROM user GROUP BY USERID
ORDER BY SUM(rank) DESC
) user, (SELECT @rownum := 0) r
Here is the SQL Fiddle.
Upvotes: 2
Reputation: 72235
You have to use variables in an outer query, like:
SELECT USERID, sum_score,
@rownum := @rownum + 1 AS UserRank
FROM (
SELECT USERID, SUM(SCORE) AS sum_score
FROM RESPOSTAS
GROUP BY USERID ) AS t
CROSS JOIN (SELECT @rownum := 0) AS v
ORDER BY sum_score DESC
Upvotes: 2