0Pricing
SQL Interview Prep · Lesson

RANK vs DENSE_RANK on Ties

The classic question on gap vs no-gap ranking when values tie.

RANK vs DENSE_RANK on Ties is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Three Ranking Functions Side by Side

SQL gives you three ranking window functions, and the single most-asked window question is how they differ on ties:

  • ROW_NUMBER — always unique; ties get arbitrary distinct numbers.
  • RANK — ties share a rank, then the next rank skips (leaves a gap).
  • DENSE_RANK — ties share a rank, then the next rank does not skip (no gap).

Being able to recite this distinction instantly is a reliable interview signal.

A Concrete Example Table

Imagine five scores ordered descending: 100, 90, 90, 80, 70. Watch how each function numbers them.

  • ROW_NUMBER: 1, 2, 3, 4, 5
  • RANK: 1, 2, 2, 4, 5 (skips 3 after the tie)
  • DENSE_RANK: 1, 2, 2, 3, 4 (no skip)

Memorize this 100/90/90/80/70 example — it answers the question on the spot.

SELECT
  score,
  ROW_NUMBER()  OVER (ORDER BY score DESC) AS rownum,
  RANK()        OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK()  OVER (ORDER BY score DESC) AS dense
FROM scores;

How RANK Computes the Gap

RANK assigns each row a rank equal to one plus the number of rows that strictly precede it in the order. Two rows tied at value 90 are each preceded by one row (the 100), so both get rank 2. The next, lower value is preceded by three rows, so it jumps to rank 4.

That is why RANK mirrors competition scoring: tied silver medalists, no bronze.

-- scores 100,90,90,80 -> RANK = 1,2,2,4
SELECT score, RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores;

How DENSE_RANK Avoids the Gap

DENSE_RANK assigns a rank equal to one plus the number of distinct values that precede the current value. After the tie at 90, only two distinct values came before the 80 (100 and 90), so it gets rank 3 — no gap.

Use DENSE_RANK when you care about distinct value positions, such as "the second-highest salary" where ties must count once.

-- scores 100,90,90,80 -> DENSE_RANK = 1,2,2,3
SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM scores;

When to Pick Each Function

A quick decision guide interviewers like to hear:

  • Need exactly one row per position (latest record, pagination)? → ROW_NUMBER.
  • Need standings where ties tie and gaps are correct (sports ranking, top-3 including ties)? → RANK.
  • Need the Nth distinct value (Nth highest salary)? → DENSE_RANK.

The wrong choice changes which rows you return, so justify your pick out loud.

Ranking Within Partitions

All three functions accept PARTITION BY to rank independently inside each group. Each partition restarts at rank 1.

Here every department ranks its own employees by salary, with ties handled by DENSE_RANK. The top earner in each department is rank 1, and equally paid employees share a rank.

SELECT
  department,
  name,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees;

Worked Example: Top 3 Including Ties

"Return the top 3 salaries per department, and if several people tie for third, include them all." This requires RANK or DENSE_RANK, not ROW_NUMBER.

Using DENSE_RANK <= 3 keeps the top three distinct salary levels and every employee at those levels — exactly what "including ties" means.

SELECT department, name, salary, salary_rank
FROM (
  SELECT department, name, salary,
         DENSE_RANK() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS salary_rank
  FROM employees
) t
WHERE salary_rank <= 3;

Worked Example: Second Highest Salary

The most famous SQL interview question. The robust answer uses DENSE_RANK so that duplicate top salaries do not push the real second value out of reach.

If three people earn the maximum, DENSE_RANK = 2 still correctly identifies the next distinct salary, whereas ROW_NUMBER = 2 would just return another top earner.

SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) t
WHERE dr = 2;

RANK vs DENSE_RANK for 'Nth Highest'

Why DENSE_RANK over RANK for Nth-highest puzzles? Because RANK leaves gaps. With salaries 100, 100, 90, the value 90 has RANK = 3 but DENSE_RANK = 2.

If the question asks for the "2nd highest distinct salary," filtering RANK = 2 would return nothing, while DENSE_RANK = 2 correctly returns 90. Always map "Nth distinct" to DENSE_RANK.

-- salaries 100,100,90
-- RANK:       1,1,3   -> WHERE rank = 2 returns NOTHING
-- DENSE_RANK: 1,1,2   -> WHERE dense_rank = 2 returns 90

Cross-Dialect Notes

Good news: ROW_NUMBER, RANK, and DENSE_RANK are SQL-standard and behave identically across PostgreSQL, SQL Server, Oracle, MySQL 8+, BigQuery, and Snowflake. The tie semantics above are universal.

One related function, PERCENT_RANK, builds on RANK to give a relative position from 0 to 1 — worth mentioning if an interviewer asks about percentile ranking.

Common Mistakes

Watch out for these:

  • Using ROW_NUMBER for "Nth highest" and missing tied rows.
  • Using RANK when the question means distinct values, then getting empty results because of gaps.
  • Forgetting ORDER BY inside OVER — ranking is meaningless without it.
  • Filtering the ranking in WHERE directly instead of in an outer query.

Quick Check

Apply the tie rules.

Recap: Gaps vs No Gaps

You can now choose the right ranking function instantly:

  • ROW_NUMBER: unique numbers, no shared ranks.
  • RANK: ties share a rank, then a gap (1,2,2,4).
  • DENSE_RANK: ties share a rank, no gap (1,2,2,3).
  • "Nth highest distinct" → DENSE_RANK; "top N including ties" → RANK/DENSE_RANK; "one row per position" → ROW_NUMBER.

Next: how to actually filter on a window result, since you cannot do it in WHERE.

Frequently asked questions

Is the “RANK vs DENSE_RANK on Ties” lesson free?

Yes — the full text of “RANK vs DENSE_RANK on Ties” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “RANK vs DENSE_RANK on Ties”?

The classic question on gap vs no-gap ranking when values tie. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “RANK vs DENSE_RANK on Ties” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OVER, PARTITION BY and ORDER BY
  2. ROW_NUMBER for Unique Sequencing
  3. RANK vs DENSE_RANK on Ties
  4. Filtering on a Window Result
← Back to SQL Interview Prep