What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

Q: What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

**Target Audience:** Beginners who have just met window functions and want the three ranking functions explained with real numbers, plus candidates who want the exact definitions. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes each example output in full. This page is the fundamentals: what each function returns and why. For choosing between them in real tasks (top-N filters, dedup, "Nth highest") see the companion page [RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?](/interview-question/rank-vs-dense-rank-vs-row-number-when-does-the-gap-matter-and-how-do-you-choose) ## 1

The problem

Question presented to candidate: "Walk me through ROW_NUMBER, RANK and DENSE_RANK. If I have scores 95, 90, 90, 85, what does each one return, and why?"

What a strong answer should cover:

All three are ranking window functions that number rows inside each partition by the window ORDER BY; they differ only in how they treat ties (rows with equal sort values, called peers)
ROW_NUMBER gives every row a distinct number (1, 2, 3, 4) and breaks ties arbitrarily unless the ORDER BY is unique
RANK gives peers the same number and then skips (1, 2, 2, 4): rank = 1 + number of rows strictly ahead
DENSE_RANK gives peers the same number with no gap (1, 2, 2, 3): dense rank = 1 + number of distinct values strictly ahead
PARTITION BY restarts the numbering per group; NULL placement in the sort differs by engine (PostgreSQL puts NULL first in DESC, SQLite last), so say NULLS LAST

Code / implementation expected: Yes — one query putting the three side by side over data with ties.

Schema used by the examples:

window
ranking

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have just met window functions and want the three ranking functions explained with real numbers, plus candidates who want the exact definitions. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes each example output in full.

This page is the fundamentals: what each function returns and why. For choosing between them in real tasks (top-N filters, dedup, "Nth highest") see the companion page [RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?](/inter

The solution is hidden so you can try first

12 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe three functions side by side over class A (ties at 90 and 85)

SQLPARTITION BY class restarts the numbering in each class

SQLWhat RANK and DENSE_RANK count: rows ahead vs distinct values ahead

PostgreSQL: a NULL score ranks first in DESC order unless you say NULLS LAST

SELECT student, score,
       RANK() OVER (ORDER BY score DESC) AS rank_default,
       RANK() OVER (ORDER BY score DESC NULLS LAST) AS rank_nulls_last
FROM exam_scores
WHERE class = 'B'
ORDER BY rank_nulls_last, student;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.