Ads by ProfitSence
Close

MySQL rank() function Code Example

Last Updated on Wednesday 5th Oct 2022

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                                 |
+-------+-----------------------------------+