Find rownum from mySQL result set for ranking

I have a table that stores scores from users of my game - what I want to be able to do if possible is find their rank using mySQL alone (because if the amount of players increases exponentially the php loop times to parse the entire database will increase dramatically).

So far I have been able to get this statement

select @rownum:=@rownum+1 'rank', s.* from top100 s, (select @rownum:=0) r order by score desc 

to return a result set with rankings applied - what I then need to开发者_StackOverflow社区 be able to do is find a single item within that using a subquery to find the players last insert_id from a previous insert.

Any help would be greatly appreciated.

SELECT t.*,
       (SELECT COUNT(*)
          FROM top100 t2
         WHERE t2.score > t.score) AS rank
  FROM top100 t
 WHERE id = LAST_INSERT_ID()