in sql, how to make a rank column based on the value of another column?

Question

say I wish to create a table like following:

user   score   rank
a      100     2   
b      200     1
c      50      3
d      50      3

How exactly do I create a rank column in which it updates with the new entry of record with score?

Possible duplicate of [Rank function in MySQL with Order By clause](http://stackoverflow.com/questions/10881990/rank-function-in-mysql-with-order-by-clause) — Lukasz Szozda, Nov 29 '15 at 22:25
Well, either calculate it on the fly, or in your application level code (e.g. PHP, javascript) — Strawberry, Nov 29 '15 at 22:31

score 1 · Accepted Answer · answered Nov 30 '15 at 00:06

For a small table, the easiest way is a correlated subquery:

select t.*,
       (select 1 + count(*)
        from t t2
        where t2.score > t.score
       ) as rank
from t 
order by score desc;

Note: this implements "rank" as per the rank() window function available in most databases.

1 Answers1