Ranking: ROW_NUMBER, RANK, and DENSE_RANK

The Top Performer Report

HR wanted a list of employees ranked by salary within each department. The three ranking functions handle ties differently.

The Three Functions

ROW_NUMBER()

Always assigns a unique number, even if values are identical.

`1, 2, 3, 4, 5` (Ties get arbitrary order).

RANK()

Ties get the same rank, and the next rank is skipped.

`1, 2, 2, 4, 5` (Two people tie for 2nd, next is 4th).

DENSE_RANK()

Ties get the same rank, and the next rank is NOT skipped.

`1, 2, 2, 3, 4` (Two people tie for 2nd, next is 3rd).

The Syntax

SELECT

name, department, salary,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS d_rnk

FROM employees;

Pro Tip

Use `ROW_NUMBER` when you need to pick exactly one row per group (e.g., "Get the most recent order per customer"). Use `RANK` or `DENSE_RANK` for traditional leaderboards.

*Day 63: Finding Previous and Next Values—LAG and LEAD.*