Mysql – Rank using one column. If it is the same value in 2 rows rank using another column

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.

You have to join the user table to the rank table twice

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

or

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

Give it a Try !!!