SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples
SQL RANK vs DENSE_RANK vs ROW_NUMBER comes down to one thing: ties. ROW_NUMBER never repeats a number, RANK repeats it and leaves gaps, and DENSE_RANK repeats it without gaps. See all three side by side, then use them for top-N and second-highest queries.
- SQL RANK vs DENSE_RANK: for scores 95, 90, 90, 85, RANK gives 1, 2, 2, 4 and DENSE_RANK gives 1, 2, 2, 3.
- ROW_NUMBER gives 1, 2, 3, 4: always unique, so tied rows get an arbitrary order unless you add a tie-breaker.
- For “top 2 per department”, RANK returned 3 QA rows because of a tie; ROW_NUMBER returned exactly 2.
- For the “second highest” question, RANK returned no rows in our data because two people tied for first; DENSE_RANK found it.
Ranking functions appear in every SQL interview and in many real reports: leaderboards, top sellers per region, latest record per customer. The three you meet most are ROW_NUMBER(), RANK() and DENSE_RANK(). They look identical until two rows tie. We ran every query below in SQLite 3.50.4; the syntax is standard and works in PostgreSQL, MySQL 8+, SQL Server and Oracle, though we only executed it in SQLite.
Quick answer
| Function | Ties get | Gaps after ties | Scores 95, 90, 90, 85 |
|---|---|---|---|
ROW_NUMBER() |
Different numbers | No | 1, 2, 3, 4 |
RANK() |
Same number | Yes | 1, 2, 2, 4 |
DENSE_RANK() |
Same number | No | 1, 2, 2, 3 |
Think of a race: RANK is the Olympic podium (two silvers, no bronze), DENSE_RANK is “levels” with no holes, and ROW_NUMBER is the order people crossed a turnstile.
Sample data
CREATE TABLE scores(name TEXT, dept TEXT, score INT);
INSERT INTO scores VALUES
('Asha','QA',95), ('Ravi','QA',90), ('Li','QA',90), ('Sam','QA',85),
('Noor','Dev',99), ('Tomas','Dev',99), ('Mei','Dev',80);
QA has a tie for second place and Dev has a tie for first place. Those two ties are enough to expose every difference.
All three side by side
SELECT name, score,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense
FROM scores
WHERE dept = 'QA'
WINDOW w AS (ORDER BY score DESC);
| name | score | row_num | rnk | dense |
|---|---|---|---|---|
| Asha | 95 | 1 | 1 | 1 |
| Ravi | 90 | 2 | 2 | 2 |
| Li | 90 | 3 | 2 | 2 |
| Sam | 85 | 4 | 4 | 3 |
Ravi and Li tie at 90. ROW_NUMBER separates them anyway (2 and 3). RANK gives both 2 and then jumps to 4, because two people are ahead of Sam plus one more. DENSE_RANK gives both 2 and continues at 3. The named WINDOW w clause avoids repeating the same OVER (...) three times; it is supported in SQLite, PostgreSQL and MySQL 8. For the general idea of windows, see our SQL window functions guide.
Ranking within each group
Add PARTITION BY to restart numbering for every department:
SELECT dept, name, score,
RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS dense,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY score DESC) AS rn
FROM scores;
| dept | name | score | rnk | dense | rn |
|---|---|---|---|---|---|
| Dev | Noor | 99 | 1 | 1 | 1 |
| Dev | Tomas | 99 | 1 | 1 | 2 |
| Dev | Mei | 80 | 3 | 2 | 3 |
| QA | Asha | 95 | 1 | 1 | 1 |
| QA | Ravi | 90 | 2 | 2 | 2 |
| QA | Li | 90 | 2 | 2 | 3 |
| QA | Sam | 85 | 4 | 3 | 4 |
Mei shows the gap clearly: RANK says 3 because two people beat Mei, while DENSE_RANK says 2 because there is only one higher score.
Top N per group: the choice changes the answer
Window functions cannot go in WHERE directly, so wrap the query in a subquery or CTE and filter outside:
SELECT * FROM (
SELECT dept, name, score,
RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS r
FROM scores
) WHERE r <= 2;
| Filter | Rows returned |
|---|---|
RANK() <= 2 |
Noor, Tomas (Dev); Asha, Ravi, Li (QA): 5 rows |
ROW_NUMBER() <= 2 |
Noor, Tomas (Dev); Asha, Ravi (QA): 4 rows |
With RANK, QA returned three people because Ravi and Li tied for second. With ROW_NUMBER, exactly two came back, but Li was dropped for no business reason. Decide which behaviour the report needs: “include ties” (RANK or DENSE_RANK) or “exactly N rows” (ROW_NUMBER with a tie-breaker). ROW_NUMBER filtered the same way also powers position-based SQL pagination and duplicate removal.
The second-highest trap
“Find the second highest score” is a classic interview question. Across all departments the scores are 99, 99, 95, 90, 90, 85, 80. We tried both functions:
-- DENSE_RANK: works
SELECT DISTINCT score FROM (
SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) d FROM scores
) WHERE d = 2; -- 95
-- RANK: no rows!
SELECT name, score FROM (
SELECT name, score, RANK() OVER (ORDER BY score DESC) r FROM scores
) WHERE r = 2; -- (empty)
Because Noor and Tomas tied for first, RANK jumped from 1 to 3 and no row ever had rank 2. DENSE_RANK has no gaps, so d = 2 always means “the second distinct value”. Use DENSE_RANK whenever a question says “Nth highest”. More interview questions like this are in our SQL interview questions guide.
Tip: add DISTINCT or pick one row when you need a single value, because DENSE_RANK still returns every row with that score.
Make ROW_NUMBER deterministic
When rows tie, ROW_NUMBER may number them in any order, and the order can change between runs, servers or database versions. Always add a unique tie-breaker:
ROW_NUMBER() OVER (ORDER BY score DESC, name)
In our run this put Li (2) before Ravi (3) alphabetically, and it will do so every time. A primary key is the safest tie-breaker. Non-deterministic numbering causes flaky reports and flaky tests that compare query output.
Two more ranking functions
PERCENT_RANK()gives a value from 0 to 1. In QA, Sam got 0.0, Ravi and Li 0.333 and Asha 1.0 (ordered by score ascending).NTILE(n)splits rows into n roughly equal buckets, useful for quartiles.NTILE(2)put Asha and Ravi in bucket 1 and Li and Sam in bucket 2, splitting the tied pair, so check ties when you use it.
Which one should you use?
| Task | Best choice |
|---|---|
| Leaderboard with shared places | RANK() |
| Nth highest value, price tiers | DENSE_RANK() |
| Exactly N rows per group, latest record per customer | ROW_NUMBER() + tie-breaker |
| Delete duplicates, keeping one | ROW_NUMBER() |
| Pagination by position | ROW_NUMBER() |
The duplicate case is covered step by step in our companion guide on how to delete duplicate rows in SQL.
3 practice queries with answers
1. Latest order for each customer
SELECT customer, placed, amount FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer
ORDER BY placed DESC, id DESC) AS rn
FROM orders
) WHERE rn = 1;
[('Asha', '2026-11-09', 120), ('Li', '2026-11-05', 700), ('Ravi', '2026-10-20', 90)]
Ravi had two orders on the same day; the id DESC tie-breaker picked the later one every time.
2. Top 2 sales levels per category, ties included
SELECT category, name, sold, r FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY category ORDER BY sold DESC) AS r
FROM products
) WHERE r <= 2;
Office returned Paper and Pen (both 90, rank 1) and Stapler (rank 2); Furniture returned Chair and Lamp (30, rank 1) and Desk (rank 2). DENSE_RANK kept every tied product and still reached the second level.
3. Rank customers by total spend
SELECT customer, SUM(amount) AS total,
RANK() OVER (ORDER BY SUM(amount) DESC) AS r
FROM orders GROUP BY customer;
[('Li', 700, 1), ('Asha', 620, 2), ('Ravi', 390, 3)]
Window functions run after GROUP BY, so you can rank aggregated totals in one query. See our GROUP BY and HAVING guide for the grouping part.
Common mistakes
- Filtering in WHERE:
WHERE RANK() OVER (...) = 1is an error. Filter in an outer query. (Some engines offerQUALIFY, but it is not standard.) - Forgetting PARTITION BY: without it you rank the whole table, not each group.
- No tie-breaker with ROW_NUMBER: results can change between runs.
- Using RANK for “Nth highest”: gaps can make the target rank disappear, as shown above.
- Mixing ASC and DESC: rank 1 is the first row in the
ORDER BY, so useDESCwhen the biggest value should win.
Final thoughts
SQL RANK vs DENSE_RANK vs ROW_NUMBER is really a question of what ties should mean. Use RANK when ties share a place and later places shift, DENSE_RANK when you need consecutive levels such as “second highest”, and ROW_NUMBER when every row needs its own number, always with a tie-breaker. Run your query against data that contains ties before trusting it; that is where all three part ways.
Frequently asked questions
What is the difference between RANK and DENSE_RANK in SQL?
Both give tied rows the same rank. RANK then skips numbers, so 1, 2, 2, 4, while DENSE_RANK continues without gaps, so 1, 2, 2, 3.
When should I use ROW_NUMBER instead of RANK?
Use ROW_NUMBER when every row needs a unique number, such as keeping one row per group, taking exactly N rows or paginating. Add a unique tie-breaker to the ORDER BY.
How do I find the second highest salary in SQL?
Use DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery and filter where the rank equals 2. RANK can return nothing when the top value is tied.
Can I use RANK in a WHERE clause?
No. Window functions are calculated after WHERE, so compute the rank in a subquery or CTE and filter in the outer query.
Does MySQL support DENSE_RANK?
Yes, MySQL 8.0 and later support RANK, DENSE_RANK and ROW_NUMBER. Older MySQL 5.7 versions do not have window functions.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.