ROW_NUMBER, RANK, DENSE_RANK

These window functions assign ranks or numbers to rows in a result set based on ordering, but behave slightly differently when there are ties.

ROW_NUMBER()

Assigns a unique sequential number to each row, regardless of ties.

RANK()

Assigns the same rank to tied rows but leaves gaps after ties.

Example: If two students tie for rank 1, the next rank will be 3.

DENSE_RANK()

Assigns the same rank to ties but does not leave gaps.

Example: If two students tie for rank 1, the next rank will be 2.

Example Query

SELECT name, age,
ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,
RANK() OVER (ORDER BY age DESC) AS rank,
DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM students;

Use these functions to add ordered numbering and rankings with control over how ties are handled.

‍