Search code examples
phpsqlranking

SQL Ranking with multiple columns

I'm making a leaderboard with rankings. Its using SQL to get the data. Now I'm wondering, how can I actually let it rank?

For example:

Name: Wins: Points: Skills:
Matt 1 2009 2
Mark 4 2014 8

How can I let PHP calculate what is at top and give it a rank? So that "Mark" will be at the first line because he has the most points and give it rank 1. And "Matt" at line 2 with rank 2?

Solution

  • Try this:

    SELECT name,Wins,Points,Skills,@rn := @rn + 1 as Rank
FROM TableName, (SELECT @rn := 0 ) r
ORDER BY points DESC

    Result (with the given data):

    NAME    WINS    POINTS  SKILLS  RANK
Mark    4       2014    8       1
Matt    1       2009    2       2

    See result in SQL Fiddle.