ROW_NUMBER vs RANK vs DENSE_RANK: What's the Difference?

Q: Which is better, RANK or DENSE_RANK?

Neither is "better" — they answer different questions. RANK tells you how many rows rank ahead of you (useful for leaderboard-style positions). DENSE_RANK tells you which distinct tier you're in, with no gaps. Pick based on what the number needs to represent.

Q: Can I use ROW_NUMBER without PARTITION BY?

Yes. Omitting PARTITION BY treats the entire result set as one partition, so ROW_NUMBER simply numbers every row 1, 2, 3... in the ORDER BY sequence.

Q: Why does my ROW_NUMBER top-N query return different rows each time?

If your ORDER BY doesn't fully break ties, rows with equal values can be returned in an arbitrary order across executions. Add a tiebreaker column (like a primary key) to the ORDER BY to make the result deterministic.

Q: Do these functions work with GROUP BY?

They're used instead of GROUP BY for this kind of ranking — window functions keep every individual row while still computing a value that depends on the group. GROUP BY, by contrast, collapses rows into one per group.

These three window functions all number rows in an ordered sequence — the entire difference between them comes down to how they handle ties. This article shows the difference side by side and explains when to reach for each one.

What each function does

ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions that all number rows according to an ORDER BY — the only difference between them is what happens when two rows tie on the ordering value.

ROW_NUMBER() hands out 1, 2, 3, 4... with no regard for ties — every row gets a unique number, even if two rows have identical values. RANK() gives tied rows the same number, then skips ahead by the count of ties (1, 2, 2, 4). DENSE_RANK() also gives tied rows the same number, but never skips (1, 2, 2, 3).

Side-by-side example

Ranking employees by salary within department, on the HR schema. Two employees tied at 6000 show the difference clearly.

SELECT department_id, last_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department_id = 50
ORDER BY salary DESC;

What the result looks like

Two rows tie at 6000. ROW_NUMBER keeps counting (2, 3) as if they weren't tied. RANK gives both the same rank (2) then jumps to 4, skipping the rank a non-tied row would have taken. DENSE_RANK also gives both rank 2, but the next distinct value gets rank 3 — no gap.

salary	row_num	rnk	dense_rnk
8000	1	1	1
6000	2	2	2
6000	3	2	2
5500	4	4	3

When to use which

Use ROW_NUMBER() when you need a guaranteed-unique sequence — the classic case is "top N per group": wrap it in a subquery and filter WHERE row_num <= 3 to get exactly 3 rows per partition even if there are ties at the boundary.

Use RANK() when ties should share a position and you want the rank number to reflect how many rows are ahead of a group — like a race, where two people tying for 2nd place pushes the next runner to 4th, not 3rd.

Use DENSE_RANK() when ties should share a position but you don't want gaps — useful for "top 3 distinct values" style questions, where WHERE dense_rnk <= 3 returns every row within the top 3 distinct salary levels, however many rows share them.

The classic pattern: top N per group

Window functions can't be filtered directly in WHERE — wrap the query in a subquery (or CTE) and filter the alias.

SELECT * FROM (
  SELECT department_id, last_name, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC;

Does this differ across dialects?

No — all three functions and this exact behaviour are standard SQL, supported identically in Oracle, PostgreSQL, SQL Server, MySQL 8+, and DB2. The syntax function() OVER (PARTITION BY ... ORDER BY ...) is portable across every mainstream database.

Frequently asked questions

Which is better, RANK or DENSE_RANK?