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