Auto incrementing a virtual column after a GROUP BY, ORDER BY query

Question

I've made extensive research about auto-increment before posting this but couldn't find similar case:

I have a query pulling data from a main table, grouping by player_id and ordering by points desc, therefore creating a ranking output. My aim is to make the same query, once it finishes aggregating and sorting data, create a new column 'Rank' and auto increment it so it shows 1,2,3 etc since everything is already grouped by player and ordered by points DESC.

Thanks guys.

Example of the source table :

player_id-----------points-----

---1-------------------5----------

---1-------------------10---------

---1-------------------5---------

---2-------------------20---------

---2-------------------5---------

Desired output according to this example:

Rank------player_id-------score-----

----1----------2-----------25 POINTS ---------

----2----------1-----------20 POINTS ---------

EDIT

Rownum does the job well, no need for an auto increment virtual column! see Mutnowski's accepted answer below please.

Check out http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/ it's not an example with a groupby but it should get your started. I think you need to run a query get get your results without the rank, then run another query on first that selects all and adds the rank — Matt Urtnowski, Apr 18 '12 at 20:15

Matt Urtnowski · Accepted Answer · 2012-04-19T06:53:48.537

7

Try this

SELECT @rownum:=@rownum+1 AS ‘rank’, Player_ID, Points FROM (SELECT Player_ID, SUM(Points) AS 'Points' FROM tblScores GROUP BY Player_ID ORDER BY Points DESC) AS foo, (SELECT @rownum:=0) AS foo2

I think you need to run a query to get your results without the rank, then run another query on first that selects all and adds the rank

edited Apr 19 '12 at 06:53

answered Apr 18 '12 at 20:31

Matt Urtnowski

2,556
1
18
36

You think right, and it worked like a charm. Thanks a million! ps: sorry I don't have enough rep yet to +1 your answer but I accept it of course ! – Naim Apr 18 '12 at 23:37

score -2 · Answer 2 · answered Apr 18 '12 at 20:37

-2

Applying SUM to the Points column should produce the results you want.

SELECT @rownum:=@rownum+1 AS ‘rank‘,player_id, SUM(points) 
FROM scores
GROUP BY player_id
ORDER BY SUM(points) DESC;

answered Apr 18 '12 at 20:37

DrewCo

954
2
8
20

Won't your @rownum return NULL? – Matt Urtnowski Apr 18 '12 at 20:41
Thanks DrewCo for your effort. You have omitted (SELECT @rownum:=0) r after FROM scores. – Naim Apr 18 '12 at 23:43
@Mutnowski - no, but it will continue to increment unless it is reset after the query, as Naim pointed out. – DrewCo Apr 19 '12 at 13:51

Auto incrementing a virtual column after a GROUP BY, ORDER BY query

2 Answers2