This is slightly modified from something I found @ http://www.kirupa.com/forum/archive/index.php/t-263260.html

Using high-score lists or similar you often have a large number of rows where you want to know who is in 7th place or how well is id:254 doing.
This eliminates the slow looping in php of big result-sets by making a sub-query in MySQL.

SET @rownum := 0;

SELECT * FROM (
SELECT @rownum := @rownum+1 AS rank, id, points
FROM highscores
ORDER BY points DESC
) AS highscores WHERE id = 254;