IntermediateIn-depth3 min readUpdated 2 Oct 2026

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

SHORT ANSWER

All three number rows in a given order. ROW_NUMBER always gives unique numbers 1, 2, 3, even for ties. RANK gives tied rows the same number and then skips ahead (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

Explain it simply

Think of a college merit list. Two students both score 90.

ROW_NUMBER is a queue at the fees counter. Even if two people arrive together, one is 2nd and the other is 3rd. Everyone gets a different number.
RANK is how sports results work. Both 90s share 2nd place, and the next student is 4th, because three people are ahead of them.
DENSE_RANK is a list of score levels. Both 90s are level 2, and the next score is level 3. No level number is skipped.

Example

sql

SELECT name, marks,
       ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_num,
       RANK()       OVER (ORDER BY marks DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rnk
FROM results;

name	marks	row_num	rnk	dense_rnk
Asha	95	1	1	1
Ravi	90	2	2	2
Meena	90	3	2	2
Kiran	85	4	4	3
Tanvi	80	5	5	4

Note that for Ravi and Meena, which of them gets row number 2 is not guaranteed unless you add a tie-breaker, such as ORDER BY marks DESC, name.

Top 2 students in every branch, with ties included:

sql

SELECT branch, name, marks
FROM (
  SELECT branch, name, marks,
         DENSE_RANK() OVER (PARTITION BY branch ORDER BY marks DESC) AS dr
  FROM results
) t
WHERE dr <= 2;

How it works

These are window functions. Unlike GROUP BY, they do not collapse rows. Every row stays in the output and gets an extra computed column.

The OVER (...) clause defines the window:

PARTITION BY branch splits rows into groups, and numbering restarts at 1 in each group. It is optional; without it the whole result is one group.
ORDER BY marks DESC decides the order in which numbers are given.

Window functions are calculated after WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. That is why you cannot write WHERE dr <= 2 in the same query; you wrap it in a subquery or a CTE and filter outside.

Function	Ties get	Gaps after ties	Typical use
ROW_NUMBER	Different numbers	Not applicable	Removing duplicates, pagination, "latest row per user"
RANK	Same number	Yes	Competition rankings
DENSE_RANK	Same number	No	Nth highest value, top N distinct levels

Real-world use

Keep only the latest address per customer: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and keep row 1.
Delete duplicate rows: number each duplicate group with ROW_NUMBER and delete rows numbered above 1.
Leaderboards for a coding contest: RANK, because two people with the same score tie, and the next person's position should reflect how many are ahead.
Nth highest salary, or top 3 restaurants by rating in each city on a food app: DENSE_RANK.

Common mistakes

Filtering on the window function in WHERE of the same SELECT. It does not exist yet at that stage; use a subquery or CTE.
Using ROW_NUMBER for "top 3" when ties matter. A student tied for 3rd gets dropped at random.
Leaving ties unbroken with ROW_NUMBER and expecting the same result every run. Add a unique column to ORDER BY.
Forgetting PARTITION BY when the question says "per department" or "in each city".

What to say in the interview

"All three are window functions that number rows based on the ORDER BY inside OVER, optionally restarting per PARTITION BY group. ROW_NUMBER gives every row a unique number, even ties. RANK gives ties the same number and then leaves gaps, like 1, 2, 2, 4. DENSE_RANK gives ties the same number without gaps, like 1, 2, 2, 3. I use ROW_NUMBER for deduplication and latest-row-per-group, RANK for competition-style rankings, and DENSE_RANK for Nth highest value problems. To filter on them, I wrap the query in a CTE or subquery."

Follow-up questions

How would you delete duplicate rows from a table using ROW_NUMBER?
What do NTILE, LAG and LEAD do?
How is a window function different from GROUP BY?

Go deeper

Read these once the basics above feel easy.

Removing duplicates while keeping the smallest id per email, in PostgreSQL:

sql

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t
  WHERE rn > 1
);

In MySQL, a DELETE cannot select from the same table directly in a subquery, which is why the extra derived table level matters there; the version above already includes it.

#sql #window-functions #ranking