coolester man coolester man - 4 years ago 137
PHP Question

show user's the position after sorting based on score

I have a table which has the id column and score column, I want to sort the table based on score column and then find the specific user who is loading the page and show him his/her position. for example, tell him "your position is 40th".
Well I know how to sort a query:

SELECT id,score FROM `table` ORDER BY `score` DESC


But after the sort how can I find an specific id's position?

Answer Source

You don't need an order by for this. Instead:

select 1 + count(*)
from table t
where t.score > (select t2.score from table t2 where id = $id);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download