[SOLVED] Get rank by high score

[jstngk]

I have a very large table with a single row for each user that holds a game score. For example the table is structure is like this (simplified for example):

 

|id|username|score|

 

|0|bob|556221

|1|Samantha|232

|2|levi|54645

 

What I want to do is just select a single row such as with:

 

"SELECT `score` FROM `highscore` WHERE `username` = 'bob' LIMIT 1"

 

However I want to know what rank they are by score. So for example I want to use the SELECT statement above to get bob’s score but I also want to know he is ranked #23 by the score field. It would be easy if we were only talking a few rows, as you could just pull the entire table and loop through it real quick, but I am talking thousands and thousands of rows and growing. What would be the most effective way to do this? Thank you ahead of time I am new to phpfreaks.

 

[kickstart]

Hi

 

Very crude way of doing it:-

 

"SELECT `score`, (SELECT COUNT(username) FROM `highscore` b WHERE b.highscore > a.highscore ) FROM `highscore` a WHERE `username` = 'bob' LIMIT 1"

 

All the best

 

Keith

[kickstart]

Hi

 

Possibly more elegant way of doing it (excuse any typos), but I suspect fair inefficient.

 

SELECT z.*, x.Rank
FROM `highscore` z
INNER JOIN (SELECT a.username, a.score, @num := @num + 1 AS Rank from `highscore` a, (SELECT @num := 0) d
order by a.score) x
ON z.username = x.username
WHERE z.username = 'bob' 
LIMIT 1

 

All the best

 

Keith

[jstngk]

Hi

 

Possibly more elegant way of doing it (excuse any typos), but I suspect fair inefficient.

 

SELECT z.*, x.Rank
FROM `highscore` z
INNER JOIN (SELECT a.username, a.score, @num := @num + 1 AS Rank from `highscore` a, (SELECT @num := 0) d
order by a.score) x
ON z.username = x.username
WHERE z.username = 'bob' 
LIMIT 1

 

All the best

 

Keith

Sorry it took so long for me to get back, this is awesome THANK YOU so much ++