CODEWITHSUNDEEP
rownum
Naim
Naim

Reputation: 463

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

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.

Upvotes: 3

Views: 2306

Answers (2)

Matt Urtnowski
Matt Urtnowski

Reputation: 2566

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

Upvotes: 7

DrewCo
DrewCo

Reputation: 954

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;

Upvotes: -2

Related Questions