Menu

Toshimaru's Blog

MySQL rank() function

Since MySQL 8.0, rank() window function is available.

Table

CREATE TABLE scores (score INT)
select * from scores;
+-------+
| score |
+-------+
| 1     |
| 2     |
| 100   |
| 5     |
| 3     |
| 100   |
+-------+

rank()

Rank of current row within its partition, with gaps

select score, rank() over (order by score desc)
from scores
order by score desc;
+-------+-----------------------------------+
| score | rank() over (order by score desc) |
+-------+-----------------------------------+
| 100   | 1                                 |
| 100   | 1                                 |
| 5     | 3                                 |
| 3     | 4                                 |
| 2     | 5                                 |
| 1     | 6                                 |
+-------+-----------------------------------+

dense_rank()

Rank of current row within its partition, without gaps

select score, dense_rank() over (order by score desc)
from scores
order by score desc;
+-------+-----------------------------------------+
| score | dense_rank() over (order by score desc) |
+-------+-----------------------------------------+
| 100   | 1                                       |
| 100   | 1                                       |
| 5     | 2                                       |
| 3     | 3                                       |
| 2     | 4                                       |
| 1     | 5                                       |
+-------+-----------------------------------------+

Reference

Load more