CODEWITHSUNDEEP
mysql
user1708530
user1708530

Reputation:

Add a Column in Query

Here is my query:

SET @rank=0;

SELECT @rank := @rank +1 AS rank_id, name, SUM(points) AS points
  FROM battle_points
  WHERE category =  'test'
  AND user_id !=0
  GROUP BY user_id
  ORDER BY points DESC;

I'd like to add a column rank based on the total points. With this query, the points are fine but the rank_id virtual column doesn't match up.

For example, the top user with the most points has rank 26, yet the rank_id column has a value of 24.

How do I matchup the rank_id column with the points column?

Note: while I am fully versed in PHP, I need a solution for MySQL only.

Upvotes: 1

Views: 56

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

You are on the right path, but you need to put the main query in a subquery so that the ordering occurs before the rank calculation, like so:

SET @rank=0;
SELECT @rank := @rank +1 AS rank_id, mainQ.* 
FROM (
   SELECT name, SUM(points) AS points
   FROM battle_points
   WHERE category =  'test'
      AND user_id !=0
   GROUP BY user_id
   ORDER BY points DESC
) AS mainQ
;

Edit: Qualified * to mainQ.*.

Upvotes: 1

Related Questions