ROW_NUMBER vs RANK vs DENSE_RANK in SQL

Posted by Kyle Hankinson November 20, 2023

---

SQL window functions ROW_NUMBER(), RANK(), and DENSE_RANK() all assign numbers to rows, but they handle ties differently. Understanding when to use each one is essential for pagination, top-N queries, and deduplication.

The Differences at a Glance

Consider a table of exam scores:

student	score
Alice	95
Bob	92
Carol	92
Dave	88

SELECT student, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_scores;

student	score	row_num	rank	dense_rank
Alice	95	1	1	1
Bob	92	2	2	2
Carol	92	3	2	2
Dave	88	4	4	3

• ROW_NUMBER: Always unique. Ties get arbitrary (but deterministic) ordering. Bob gets 2, Carol gets 3.
• RANK: Ties get the same number, but the next rank skips. Both Bob and Carol get 2, Dave gets 4 (not 3).
• DENSE_RANK: Ties get the same number, no gaps. Both Bob and Carol get 2, Dave gets 3.

When to Use Each

ROW_NUMBER for Pagination

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM posts
) ranked
WHERE rn BETWEEN 21 AND 40;

Every row gets a unique number, making it perfect for page boundaries.

ROW_NUMBER for Deduplication

To keep only the latest record per user:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
    FROM user_profiles
)
SELECT * FROM ranked WHERE rn = 1;

RANK for Competition-Style Rankings

When ties should share the same position and the next position should reflect the number of people ahead:

SELECT player, points,
       RANK() OVER (ORDER BY points DESC) AS position
FROM leaderboard;

If two players tie for 2nd place, the next player is 4th — just like in sports.

DENSE_RANK for Top-N Categories

When you want the top 3 distinct salary levels, regardless of how many people share each level:

SELECT * FROM (
    SELECT department, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr <= 3;

PARTITION BY

All three functions support PARTITION BY to restart numbering within groups:

SELECT department, employee, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

This ranks employees within each department independently.

Database Compatibility

These functions work in:

• MySQL 8.0+
• PostgreSQL 8.4+
• SQL Server 2005+
• Oracle 8i+
• SQLite 3.25+

The syntax is identical across all of them.

Using SQLPro Studio

SQLPro Studio supports all of these databases, so you can write and test window function queries regardless of which database engine you use. The autocomplete feature will suggest ROW_NUMBER, RANK, and DENSE_RANK as you type.

---