How does the rank within a group query work?

PARTITION BY region restarts the numbering for each region. ROW_NUMBER gives every row a unique number, even when revenue ties. RANK gives tied rows the same number and then skips (1, 2, 2, 4); DENSE_RANK does not skip (1, 2, 2, 3).

What commonly goes wrong with rank within a group in SQL?

With ties, ROW_NUMBER picks one row arbitrarily unless you add a tie-breaker to ORDER BY.

Rank Within a Group in SQL

Ranking within a group numbers rows from best to worst inside each category, using ROW_NUMBER, RANK or DENSE_RANK.

Example query

This example assumes: rep_revenue(region text, rep text, revenue numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT region,
       rep,
       revenue,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
       RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
FROM rep_revenue
ORDER BY region, row_num;

How it works

PARTITION BY region restarts the numbering for each region.
ROW_NUMBER gives every row a unique number, even when revenue ties.
RANK gives tied rows the same number and then skips (1, 2, 2, 4); DENSE_RANK does not skip (1, 2, 2, 3).

MySQL, BigQuery, SQL Server and SQLite differences

ROW_NUMBER, RANK and DENSE_RANK work in PostgreSQL, MySQL 8+, SQL Server, BigQuery and SQLite 3.25+.

Common mistakes

With ties, ROW_NUMBER picks one row arbitrarily unless you add a tie-breaker to ORDER BY.
Choosing RANK when you wanted a 'top 3 distinct values' returns more than three rows when there are ties; use DENSE_RANK.
Nulls sort first or last depending on the database; add NULLS LAST if a missing value should not rank first.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Rank sales reps by revenue within each region.
Who is number one in each region by revenue?