Ranking Data with SQL: RANK, DENSE_RANK, and ROW_NUMBER Explained

"Who are our top 10 customers?" "What's the rank of each student in their class?" "Show me the 3 best-selling products per category."

These questions all require ranking data. SQL provides three powerful window functions for this, and choosing the right one matters.

A Real Business Scenario: Product Leaderboards and Top Performers

Ranking functions show up any time a business wants ordered winners instead of raw totals.

Typical examples:

• the top 10 customers by revenue
• the best-selling products inside each category
• the most recent event per user
• the highest-priority ticket within each queue

The tricky part is not generating numbers. It is deciding how ties should behave and whether you need exactly N rows or the top N ranks.

The Three Ranking Functions

Function	Handles Ties	Gaps After Ties
ROW_NUMBER()	No - assigns unique numbers	N/A
RANK()	Yes - same rank for ties	Yes - skips numbers
DENSE_RANK()	Yes - same rank for ties	No - consecutive

Let's see each in action.

Interactive Example: Student Scores

Look at Bob and Charlie - both scored 90:

• ROW_NUMBER: Bob=2, Charlie=3 (arbitrary order for ties)
• RANK: Both=2, Diana=4 (gap after tie)
• DENSE_RANK: Both=2, Diana=3 (no gap)

When to Use Each Function

ROW_NUMBER: Unique Numbering

Use when you need exactly one number per row, regardless of ties:

-- Pagination: Get rows 11-20
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at) as rn
  FROM posts
) WHERE rn BETWEEN 11 AND 20;

RANK: Competition-Style Ranking

Use for leaderboards where ties share rank but the next person "drops":

-- Olympic-style: Gold, Gold, Bronze (no Silver)
SELECT athlete, time,
  RANK() OVER (ORDER BY time) as place
FROM race_results;

DENSE_RANK: Consecutive Ranking

Use when you want no gaps in your ranking sequence:

-- Top 3 salary levels (might be more than 3 people)
SELECT * FROM (
  SELECT name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
  FROM employees
) WHERE salary_rank <= 3;

A Common Mistake: Asking for "Top N" Without Defining Tie Behavior

This is the most common source of ranking bugs in production reporting.

If someone asks for the "top 3 products per category," there are at least two different meanings:

• exactly 3 rows per category
• all rows whose rank is within the top 3 values

Those are not the same query.

• ROW_NUMBER() gives exactly N rows, but it breaks ties arbitrarily unless you add a secondary sort.
• RANK() and DENSE_RANK() respect ties, but they may return more than N rows.

Until that rule is explicit, the report can look correct while still violating the business expectation.

Ranking Within Groups: PARTITION BY

The real power comes when ranking within categories:

Notice how ranking restarts at 1 for each department!

Top N Per Group Pattern

One of the most useful SQL patterns - get the top N items from each category:

Practical Example: Sales Leaderboard

Quick Reference Table

Scenario	Best Function
Pagination	ROW_NUMBER()
Leaderboard with gaps	RANK()
Top N salary levels	DENSE_RANK()
Exactly N rows per group	ROW_NUMBER()
Olympic medal ranking	RANK()
Finding duplicates	ROW_NUMBER()

Common Mistakes to Avoid

1. Forgetting ORDER BY: Ranking without ORDER BY gives unpredictable results.

2. Using RANK for pagination: If there are ties, you might get more rows than expected.

3. Ignoring tie-breakers: Add secondary ORDER BY columns to control tie behavior:

   ROW_NUMBER() OVER (ORDER BY score DESC, created_at ASC)
   

Boundary and Performance Notes

Ranking queries are expressive, but they are not free:

• window functions usually require sorting within each partition
• very large partitions can become expensive without selective filtering or supporting indexes
• ranking on unstable source tables can produce confusing results if the ordering column changes frequently
• wide leaderboard queries often mix ranking with joins and aggregates, which makes grain mistakes easier to hide

The safest pattern is to define the comparison set clearly first, aggregate if needed, and only then apply the ranking function.

When NOT to Reach for a Ranking Function

Avoid using ranking as the default solution when:

• you only need MIN, MAX, or one latest row that can be expressed more simply
• the business question is about percentiles or buckets rather than ordinal position
• a lateral join or grouped aggregate is easier to read and maintain
• ties should be broken by explicit business rules rather than arbitrary row ordering

Ranking is powerful, but it should reflect the business rule rather than replace it.

Official References

• PostgreSQL window function documentation for ROW_NUMBER, RANK, and DENSE_RANK.
• SQLite window functions documentation for the SQLite syntax and behavior used in examples.
• PostgreSQL tutorial on window functions for a conceptual explanation of partitions and ordered-row calculations.

Conclusion

• ROW_NUMBER(): One unique number per row, perfect for pagination and deduplication.
• RANK(): Ties share rank, gaps follow - ideal for competitions.
• DENSE_RANK(): Ties share rank, no gaps - great for "top N levels" queries.

Add PARTITION BY to rank within groups, and these functions cover most leaderboard and top-N patterns. The real skill is choosing the function that matches the tie rule the business actually cares about.

Related Articles

• Understanding Window Functions: A Practical Guide for the window-function foundation behind ranking.
• Mastering SQL LEAD and LAG Functions for Row Comparisons for another ordered-row analysis pattern built on the same mental model.
• The Power of SQL LATERAL Joins (and CROSS APPLY) for the per-group top-N alternative when lateral-style execution fits better than full ranking.