0

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?

Matt
  • 69
  • 7
  • You need to do it with php or sql? – Raging Bull May 11 '14 at 10:45
  • I would go for a query which will be much easier than doing it in php. But we need to see the query you have. – Raging Bull May 11 '14 at 10:48
  • Here is the complete code: https://gist.github.com/matthijs110/6d3db4c5a5a43699f58c The tables are different then my example. But the highest wins points needs to be at top and the highest one needs to get Rank 1,the the next highest rank 2. – Matt May 11 '14 at 10:49
  • Your query says `ORDER BY Wins DESC`, so the result shown here doesn't make sense. – Raging Bull May 11 '14 at 10:51

3 Answers3

0

You can do it in MySQL like this

select t.*, 
       @rank := @rank + 1 as rank
from your_table t
cross join (select @rank := 0) r
order by points desc
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • That didn't seem to work :/ https://gist.github.com/matthijs110/524f2e19a5dc7ebac290 – Matt May 11 '14 at 10:55
0

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.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • I get this error then: Notice: Trying to get property of non-object in /Applications/XAMPP/xamppfiles/htdocs/Minecraft-User-Info/leaderboard/index.php on line 86 Line 86: if ($result->num_rows > 0) { – Matt May 11 '14 at 10:57
  • @Matt: See [**this question**](http://stackoverflow.com/questions/5891911/trying-to-get-property-of-non-object-in). – Raging Bull May 11 '14 at 11:04
0

You don't mention DBMS, and the mysql trick presented above does not work, so I assume a DBMS with support for OLAP (psql, db2, oracle, mssql among other supports them)

select name,Wins,Points,Skills from ( SELECT name,Wins,Points,Skills , rank() over (order by points desc) as rnk FROM TableName ) order by rnk

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32