CODEWITHSUNDEEP
mysqlsql
Bango
Bango

Reputation: 288

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

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?

Upvotes: 2

Views: 2176

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions