Mysql – Getting Ranked Position – SQL – Simple Query

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

Track the facts that drive your ranking system in normalized tables according to what makes sense for those facts without regard to how it drives your ranking system.

Then have a separate table or tables that converts your basic facts into a points system that allows you to do simple sorts for ranking.

The reason to keep these things separate is that your basic facts are driven by some kind of usage of your system, whatever that may be. You want the code for tracking this usage to be clean and maintainable. Even if you didn't have a ranking system, these facts would exist. The logic to maintain these facts is independent of the logic to calculate rankings. The ranking rules are an extra layer on top of the facts. Also, your rules for ranking could (easily) change over time, so you don't want to tangle up your basic facts with less stable rules.