RANK / DENSE_RANKFunctions
Buy Me A Coffee

Window functions that assign a rank to each row within a partition. RANK skips rank numbers after ties; DENSE_RANK does not skip.

Window functions that assign ordinal ranks to rows: ROW_NUMBER (sequential), RANK (with gaps), DENSE_RANK (without gaps). Essential for top-N queries, deduplication, and sorting by competition rank. Combine with DISTINCT ON in PostgreSQL for efficient top-N per group.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 RANK(), DENSE_RANK(), and PERCENT_RANK() all added in MySQL 8.0. Not available in 5.7 or earlier — requires workarounds with user variables.
PostgreSQL ✓ Supported 8.4 RANK(), DENSE_RANK(), PERCENT_RANK(), CUME_DIST() all supported. Standard OVER clause with PARTITION BY and ORDER BY.
SQL Server ✓ Supported 2005 RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK() all available since 2005. Standard syntax.
Oracle ✓ Supported 8i RANK() and DENSE_RANK() have two forms in Oracle: the standard analytic (window) form with OVER(), and a unique aggregate form — RANK(value) WITHIN GROUP (ORDER BY col) — which computes the hypothetical rank of a given value without a full window scan.
SQLite ✓ Supported 3.25.0 RANK() and DENSE_RANK() added in SQLite 3.25.0 (September 2018). Standard syntax.

Details

The difference: if three rows tie for 2nd place, RANK gives them all 2 and then skips to 5; DENSE_RANK gives them all 2 and continues with 3. DENSE_RANK is usually what you actually want for leaderboards. Oracle's hypothetical aggregate form (RANK(val) WITHIN GROUP) is unique — it answers 'what rank would this value get?' without needing a full result set.

Standard Syntax

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

RANK(), DENSE_RANK(), and PERCENT_RANK() all added in MySQL 8.0. Not available in 5.7 or earlier — requires workarounds with user variables.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk, PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rnk FROM scores; -- With partition: SELECT name, dept, score, RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS dept_rank FROM scores;

PostgreSQL

RANK(), DENSE_RANK(), PERCENT_RANK(), CUME_DIST() all supported. Standard OVER clause with PARTITION BY and ORDER BY.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk FROM scores; -- Partition by department: SELECT name, dept, score, DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS dept_rank FROM scores;

SQL Server

RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK() all available since 2005. Standard syntax.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk FROM scores; SELECT name, dept, score, RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS dept_rank FROM scores;

Oracle

RANK() and DENSE_RANK() have two forms in Oracle: the standard analytic (window) form with OVER(), and a unique aggregate form — RANK(value) WITHIN GROUP (ORDER BY col) — which computes the hypothetical rank of a given value without a full window scan.

-- Analytic (standard) form: SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk FROM scores; -- Hypothetical aggregate (Oracle-unique): SELECT RANK(85) WITHIN GROUP (ORDER BY score DESC) AS hyp_rank FROM scores;

SQLite

RANK() and DENSE_RANK() added in SQLite 3.25.0 (September 2018). Standard syntax.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk FROM scores;