Geek Answers Handbook

Get rank based on scores from MySql disordered database when given username

Ok, so I have a table that has the following

KEY username password score

The above columns are not in any particular order.

I want to send my database username and send me back what rank this username is based on its rating. So, for example, if I had 10 people, and the 3rd person has the highest score. When I pass the 3rd party username, I want it to send back 1.

Is it possible?

I tried such things

 $result = mysql_query("SELECT * FROM tablename where username='$username' ORDER BY score DESC");

but he doesn't seem to give me the line number

+4
source share
2 answers

This will handle ranks with the same rating.

 SELECT d.*, c.ranks FROM ( SELECT Score, @rank: =@rank +1 Ranks FROM ( SELECT DISTINCT Score FROM tableName a ORDER BY score DESC ) t, (SELECT @rank:= 0) r ) c INNER JOIN tableName d ON c.score = d.score // WHERE d.username = 'Helen'

eg

 KEY username password score Ranks 1 Anna 123 5 3 2 Bobby 345 6 2 3 Helen 678 6 2 4 Jon 567 2 4 5 Arthur ddd 8 1

to improve performance, add INDEX in the Score column,

 ALTER TABLE tableName ADD INDEX (Score)
+3
source 
 SELECT (SELECT COUNT(*)+1 FROM tablename WHERE score > t.score) as rank, * FROM tablename t where username='$username'

ORDER BY in your query is useless since you are returning only one row.

+2
source

More articles:


All Articles