CODEWITHSUNDEEP
mysqlsql-order-by
DANIEL
DANIEL

Reputation: 547

SQL - How to make a Ranking - with Sum and Order?

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

Answers (2)

Darshan Mehta
Darshan Mehta

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

Giorgos Betsos
Giorgos Betsos

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

Demo here

Upvotes: 2

Related Questions