Window Functions · Advanced

DENSE_RANK

ranking rows without gaps after ties.

This topic uses window functions or CTEs; MySQL requires version 8.0 or later.

Overview

DENSE_RANK assigns the same rank to tied ordering values and keeps the next rank consecutive, unlike RANK. Use PARTITION BY for ranking within groups. If tied rows need stable display order, add a unique key to the outer ORDER BY without adding it to the ranking expression.

Basic syntax

SELECT name, DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk FROM orders;

Examples

DENSE_RANK example

The example ranks rows by descending amount. Equal amounts share a rank and the following amount does not skip a number.

Sample table

Table：orders

orders
name	amount
Alice	1200
Bob	900
Cindy	1500
David	700

Run result

result
name	rnk
Cindy	1
Alice	2
Bob	3
David	4

Common issues

How does DENSE_RANK differ from RANK and ROW_NUMBER?

With ties, DENSE_RANK has no gaps, RANK leaves gaps, and ROW_NUMBER still assigns a unique number to every row.

How do I restart ranking within each department?

Use PARTITION BY for the department and define the within-department ranking criteria in the window ORDER BY.

Does the window ORDER BY guarantee final row order?

No. It only calculates the rank; add a query-level ORDER BY for deterministic display.

Was this explanation clear?

← RANK LAG →