2
user_id | date | point
    1      20      4
    2      20      3
    3      20      2
    1      21      1
    2      21      3
    3      21      5
    1      23      2
    2      23      4
    3      23      5

And query:

SELECT user_id, SUM(point) AS point, @row:=@row+1 rank FROM users GROUP BY user_id

How to show rank in this query ?

Hai Truong IT
  • 4,126
  • 13
  • 55
  • 102
  • possible duplicate of http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table – Hassan Feb 25 '14 at 07:24

6 Answers6

2

first you need to delcare @row as variable first

Something like set @row=0; select user_id,SUM(point) as point,@row := @row + 1 as rank from users GROUP BY user_id order by SUM(point) desc;

Anant Dabhi
  • 10,864
  • 3
  • 31
  • 49
0

try this

SET @row=0;
SELECT user_id, SUM(point) AS point, @row:=@row+1 rank FROM users GROUP BY user_id
Satish Sharma
  • 9,547
  • 6
  • 29
  • 51
0

You can work around the declaring part using this way:

SELECT user_id, SUM(point) AS point, @row:=@row+1 rank FROM users, (SELECT @row := 0) r GROUP BY user_id
Abed Hawa
  • 1,372
  • 7
  • 18
0

I guess you've to write it like:

SELECT user_id, SUM(point) AS point, @row:=@row+1 AS rank FROM users GROUP BY user_id JOIN (SELECT @row := 0) r;
GuyT
  • 4,316
  • 2
  • 16
  • 30
0

Try this..

SELECT user_id,points, @row:=@row+1 AS Row 
from (
  SELECT user_id, 
    SUM(point) AS points  rank 
  FROM users 
  GROUP BY user_id 
  Sort by points);
PaDi
  • 255
  • 1
  • 5
  • 16
0

Should have an AS to alias @row to rank, and an order by on point to get them in the right order to rank:-

SELECT user_id, SUM(point) AS point, @row:=@row+1 AS rank 
FROM users 
CROSS JOIN (SELECT @row:=0) Sub1
GROUP BY user_id
ORDER BY point DESC
Kickstart
  • 21,403
  • 2
  • 21
  • 33