1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

8 min read
SQL RANK vs DENSE_RANK vs ROW_NUMBER compared

Key takeaways

  • 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 (...) = 1 is an error. Filter in an outer query. (Some engines offer QUALIFY, 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 use DESC when 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →