ROW_NUMBER vs RANK vs DENSE_RANK: SQL Ranking Ties Explained

ROW_NUMBER, RANK, and DENSE_RANK all assign positions with a window function. The choice matters only when values tie, which is exactly when a quick ranking query can return the wrong people.

Use ROW_NUMBER() when the question asks for a fixed number of rows and you can define a deterministic tie-breaker. Use RANK() when equal scores share a position and the next position should reflect how many rows came before it. Use DENSE_RANK() when equal scores share a position and the next distinct score should use the next number without a gap.

Key takeaways

• A peer group is the set of rows tied on every expression in a window's ORDER BY.
• RANK() leaves gaps after a tie; DENSE_RANK() counts score levels without gaps.
• Add a stable secondary sort key for ROW_NUMBER() when ties must be reproducible.
• The ORDER BY inside OVER calculates a rank. The final query ORDER BY only displays rows.

The SQL window functions practice hub is the right place to apply these patterns. For focused repetitions, try Department Top-3 Earners and Top Three Sellers per Warehouse.

One compact leaderboard with a real tie

The examples use one row per analyst in a weekly leaderboard:

Bo and Cy are peers when a window orders only by score DESC: each has the same score, so neither is ahead of the other for that ranking. Ava is the first score level, Bo and Cy are the second, Di is the third, and Eli is the fourth.

See all three functions side by side

This query intentionally gives ROW_NUMBER() a secondary key, analyst ASC. That makes the single-row choice between Bo and Cy deterministic. The other two windows order by score alone, so they keep Bo and Cy in the same peer group.

SELECT entrant_id,
       analyst,
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC, analyst ASC) AS row_num,
       RANK() OVER (ORDER BY score DESC) AS score_rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS score_level
FROM leaderboard
ORDER BY score DESC, analyst ASC;

ROW_NUMBER() assigns each row a different number. It is useful for selecting exactly one row per position, but it does not represent a tied score as a shared rank. Here Bo is before Cy because the query says analyst ASC breaks the tie. Without that extra expression, their relative row numbers are not a dependable business rule.

RANK() gives Bo and Cy both position 2. Di is then position 4 because two rows occupy places 2 and 3. That gap is correct for a competition-style standing: Di has three rows ahead, even though there are only two higher score values.

DENSE_RANK() also gives Bo and Cy 2, but Di receives 3. It counts distinct score levels, not rows. That is usually the clearer answer for "third-highest score" or "top three distinct values."

Top two rows is not top two score levels

The phrase "top two" is incomplete until you decide whether it means two rows or two distinct score levels.

If a dashboard has space for exactly two people, ROW_NUMBER() is appropriate. The secondary key makes the cutoff repeatable:

WITH numbered AS (
  SELECT entrant_id,
         analyst,
         score,
         ROW_NUMBER() OVER (ORDER BY score DESC, analyst ASC) AS row_num
  FROM leaderboard
)
SELECT entrant_id,
       analyst,
       score
FROM numbered
WHERE row_num <= 2
ORDER BY score DESC, analyst ASC;

Cy has the same score as Bo but is excluded because the request is for two rows, not shared positions. That outcome is defensible only because the business rule permits the stated tie-breaker. If it does not, ask for one before writing the query.

For the top two score levels, use DENSE_RANK() and filter after the window calculation:

WITH ranked_scores AS (
  SELECT entrant_id,
         analyst,
         score,
         DENSE_RANK() OVER (ORDER BY score DESC) AS score_level
  FROM leaderboard
)
SELECT entrant_id,
       analyst,
       score,
       score_level
FROM ranked_scores
WHERE score_level <= 2
ORDER BY score DESC, analyst ASC;

| entrant_id | analyst | score | score_level | | --- | --- | --- | | 1 | Ava | 100 | 1 | | 2 | Bo | 90 | 2 | | 3 | Cy | 90 | 2 |

This time all rows at the cutoff score stay in the result. RANK() <= 2 would return the same rows for this particular cutoff, but DENSE_RANK() expresses the actual rule: include the first two distinct score levels. The difference matters at later cutoffs. With these scores, RANK() <= 3 still stops after Bo and Cy because the next rank is 4, while DENSE_RANK() <= 3 includes Di.

Keep calculation order and display order separate

There are two different ORDER BY clauses in the examples, and they have different jobs.

• ORDER BY score DESC inside OVER (...) decides who is a peer and how the rank is calculated.
• ORDER BY score DESC, analyst ASC at the end decides how readers see the completed result.

Changing only the final sort cannot change a calculated rank. For example, you can display the leaderboard alphabetically after calculating RANK() OVER (ORDER BY score DESC), and Bo and Cy will still both have rank 2. Conversely, adding analyst inside the RANK() window would break their peer group and give them different ranks. Put a tie-breaker inside a ranking window only when the rank itself must distinguish the tied rows.

A practical selection rule

Before committing to a function, say the cutoff in plain language: "two rows," "two positions," or "two distinct scores." That forces the tie rule into the open and prevents the bug of returning fewer or more rows than the request means.

For the exact definitions and peer-group behavior, consult the current PostgreSQL window-functions documentation. It is a useful primary reference when a database-specific detail matters.