supposed the table is ordered `score` desc, `id` asc
get the 10th rank from the top
order by `score` desc, `id` asc
limit 9, 1";
get rank number by a given score 2000
"select count(*)+1 as rank
where `score` > 2000";
// what if 2000 is non-existent, is it still considered to have a rank number
ramie's reply uses multi-query which requires php 5. that's fine when you design the app for own use. but if you are making an app for other ppl to use, lowered the requirement to php 4.4, mysql 4.1 or something like that.
assume there is no function rank() in mysql that will do
select *, rank() from scoretable order by score desc, id asc
this is what i came up. everything is in a single sql statement.
get rank number by a given scoreid 123, assume possible identical scores
"select a.rank+b.rank as rank
from (SELECT count(*)+1 as rank FROM scoretable where score > (select score from scoretable where id = 123)) as a
(SELECT count(*) as rank FROM scoretable where score = (select score from scoretable where id = 123) and id < 123 ) as b"
if there is a better solution i would love learn