CODEWITHSUNDEEP
mysqlranking
sveti petar
sveti petar

Reputation: 3787

MySQL query to sort by one column and generate its ranking (equivalent to RANK OVER PARTITION oracle)

I have a MySQL table like this:

id | points | rank | league_id
______________________________
1  | 84     | 0    | 1
2  | 55     | 0    | 1
3  | 104    | 0    | 1
4  | 123    | 0    | 2

What I want to accomplish is the following:

id | points | rank | league_id
______________________________
1  | 84     | 2    | 1
2  | 55     | 3    | 1
3  | 104    | 1    | 1
4  | 123    | 1    | 2

So - use the rank column to store ranks based on number of points, grouped by league_id. This may seem redundant but I need it for my purpose (it's for a fantasy sports website and having a rank column greatly simplifies a lot of PHP code and reduces the number of needed queries throughout the script).

I'm aware of the obvious solution - iterate through the rows, grouping by league_id and updating the ranks one by one. What I'm wondering is, is there a more efficient solution?

Upvotes: 2

Views: 761

Answers (1)

Wrikken
Wrikken

Reputation: 70460

OTOH, so test first on a throw-away database, prepare to load backup:

  SET @rank = 1;
  SET @league = 0;

  UPDATE tablename
  SET 
      rank = @rank := IF(@league = league_id,@rank+1,1),
      league_id = @league := league_id
  ORDER BY league_id,points DESC;

Upvotes: 2

Related Questions