Window Functions · Advanced

RANK

ranking rows with gaps for ties.

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

Overview

RANK is a window ranking function: equal sort values receive the same rank and the following rank skips occupied positions, such as 1, 1, 3. It does not collapse rows, and PARTITION BY can restart ranking per group. When ties need deterministic treatment, decide whether the business requires shared ranks or ROW_NUMBER instead.

Basic syntax

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

Examples

RANK example

Rank orders by amount descending and observe how equal amounts share a rank and leave a gap afterward.

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

Do window functions reduce row count?

No. They keep detail rows while calculating ranks, neighboring values, or running results.

Why can ranking appear unstable?

When window sort values tie, add a stable unique tie-breaker.

Does window ordering guarantee display order?

No. Add a query-level ORDER BY to guarantee the final output order.

Was this explanation clear?

← ROW_NUMBER DENSE_RANK →