RANK / DENSE_RANK

Functions
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

Filter by Database
SQL RANK / DENSE_RANK Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 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 Native 8.4 RANK(), DENSE_RANK(), PERCENT_RANK(), CUME_DIST() all supported. Standard OVER clause with PARTITION BY and ORDER BY.
SQL Server Native 2005 RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK() all available since 2005. Standard syntax.
Oracle Native 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 Native 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: Native since 8.0 PostgreSQL: Native since 8.4 SQL Server: Native since 2005 Oracle: Native since 8i SQLite: Native since 3.25.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;