MySQL rank() function Code Example

Last Updated On Friday 27th Aug 2021

Table

	CREATE TABLE ages (age INT)
	
	select * from ages;
+-------+
|  age  |
+-------+
| 18    |
| 21    |
| 17    |
| 28    |
| 28    |
+-------+
	

rank()

Rank of current row within its partition, with gaps

	select age, rank() over (order by age desc)
from ages
order by age desc;
	
	+-------+-----------------------------------+
|  age  | rank() over (order by age desc)   |
+-------+-----------------------------------+
| 28   | 1                                 |
| 28   | 1                                 |
| 21   | 3                                 |
| 18   | 4                                 |
| 17   | 5                                 |
+-------+-----------------------------------+
	

dense_rank()

Rank of current row within its partition, without gaps

	select age, dense_rank() over (order by age desc)
from ages
order by age desc;
	
	+-------+-----------------------------------+
|  age  | rank() over (order by age desc)   |
+-------+-----------------------------------+
| 28   | 1                                 |
| 28   | 1                                 |
| 21   | 2                                 |
| 18   | 3                                 |
| 17   | 4                                 |
+-------+-----------------------------------+