Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL RANK() and DENSE_RANK() functions

Reference for SQL RANK() and DENSE_RANK(): rank rows with ties, understand gaps versus no gaps, rank within groups, NULL ordering and top-N with ties.

RANK() and DENSE_RANK() are window functions that assign a rank to each row according to the ORDER BY of the window. Rows with equal sort values are peers and get the same rank. The two functions differ in what comes after a tie.

RANK() leaves gaps: after two rows tied for rank 2, the next rank is 4. DENSE_RANK() never skips: the next rank is 3. Both start at 1 and restart in every PARTITION BY group. If you need a unique number for every row, use ROW_NUMBER().

SQL
SELECT name, price,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products;

Try it

SQL Three kinds of numbering side by side ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

SQL Ties in salaries across the company ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Top 5 grades, ties included ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
RANK()       OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])
DENSE_RANK() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])
Part Description
PARTITION BY Optional. Ranks restart at 1 in each partition.
ORDER BY Required in practice. Rows are ranked by these expressions; rows equal on all of them are peers.

Returns: an integer rank starting at 1. RANK() returns one plus the number of rows that sort strictly before the current row. DENSE_RANK() returns one plus the number of distinct sort values before it.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
RANK(), DENSE_RANK() 3.25+ 8.4+ 8.0+ 2005+ Yes
OVER without ORDER BY All rows get rank 1 All rows get rank 1 All rows get rank 1 Error Error
NULLs in ORDER BY Sort first ascending Sort last ascending Sort first ascending Sort first ascending Sort last ascending
Hypothetical form RANK(value) WITHIN GROUP (...) No Yes No No Yes
SQL
-- PostgreSQL, Oracle: what rank would 70000 have among current salaries?
SELECT RANK(70000) WITHIN GROUP (ORDER BY salary) FROM employees;

-- PostgreSQL, Oracle: control where NULLs rank
SELECT RANK() OVER (ORDER BY manager_id NULLS LAST) FROM employees;

Usage notes

  • Use DENSE_RANK() for "the third highest value". RANK() can skip the number 3 entirely when two rows tie for second place. Dense ranks always have a row for every number up to the maximum.
  • Use RANK() for competition-style standings (1, 2, 2, 4), where the position reflects how many rows came before.
  • Top N with ties: filter rnk <= N in an outer query. The result may have more than N rows when ties cross the boundary. For exactly N rows use ROW_NUMBER() with a tie-breaker.
  • Peers are decided by every ORDER BY expression. Adding a second sort column breaks ties and changes the ranks.
  • NULL handling: NULLs are peers of each other. Where they sort differs by database (see the table), so write NULLS FIRST/LAST where supported or sort NULLs explicitly with CASE.
  • You cannot use the rank in WHERE in the same query. Wrap it in a subquery or CTE.
  • Ascending vs descending: ORDER BY price DESC gives rank 1 to the highest price. Forgetting DESC is the most common bug.
  • Percent-style alternatives: PERCENT_RANK() and CUME_DIST() express rank as a fraction between 0 and 1.