DENSE_RANK

Hook: DENSE_RANK() is the “no empty podium spots” version of ranking: ties share a place, and the next distinct value moves to the next number.

Question: What does DENSE_RANK() do in SQL?

Answer: DENSE_RANK() is a window function that assigns a rank to each row inside a sorted set, giving the same rank to equal values. Unlike RANK(), it does not skip numbers after ties, so the ranks stay compact: 1, 1, 2, 3 instead of 1, 1, 3, 4. It is most useful when you want rank groups or “top N distinct values,” not unique row numbers.

Interview-Ready Answer: “I use DENSE_RANK() when I want tied rows to share the same rank, but I do not want gaps in the numbering. It’s a window function, so I define the order with OVER (ORDER BY ...), and I can also split the ranking by group with PARTITION BY. A good example is a leaderboard: if two players tie for first, the next player is rank 2, not rank 3.”

What it is

DENSE_RANK() is a window function (a function that computes a value across a related set of rows without collapsing them into one row). It looks at the ordering you define, finds rows with the same sort key, and gives them the same rank. The word dense means there are no gaps in the rank numbers.

How it works under the hood

1. The database first forms a window: either the whole result set, or smaller groups if you use PARTITION BY.
2. It orders rows inside each window by the columns in ORDER BY.
3. Rows with the same ordered values are called peers (equal neighbors in the sort order).
4. The engine scans the ordered rows from top to bottom. The first distinct value gets rank 1.
5. When it sees the same value again, it reuses the current rank.
6. When it sees a new distinct value, it adds 1 to the rank, with no skipped numbers.
7. The output keeps every original row, so you get one ranked row per input row.

This is why DENSE_RANK() is great for tiers, bands, and “top N distinct scores.” If three people tie for second place, they all get rank 2, and the next distinct score gets rank 3.

When to use it

• Leaderboards: sales reps, players, or stores with ties.
• Tiered reporting: A/B/C bands based on score or revenue.
• Top-N by distinct value: the top 3 score levels, not necessarily only 3 rows.
• Deduped ordering logic: when you care about unique value levels more than row count.

Compare it with similar functions

Simple memory move: think “dense = packed tightly.” Every new distinct value gets the next number, with no empty gaps left behind.

Performance and edge cases

Ranking functions are usually dominated by the sort. If the database cannot use an existing index order, it often sorts the partition and order columns first, which is roughly O(n log n) time for the sort, then O(n) to assign ranks. Memory use depends on row width and partition size; if the sort does not fit in memory, the engine may spill to disk, which can slow things down a lot on large tables.

Two practical details matter in interviews: 1) if you omit PARTITION BY, the whole result set is one partition; 2) ranking functions depend on ordering, so the ORDER BY inside OVER is the real meaning of the query. Also, NULL ordering can vary by database unless you specify NULLS FIRST or NULLS LAST where supported. Modern support is broad: PostgreSQL, SQL Server, Oracle, MySQL 8.0+, and SQLite 3.25+ support window functions; older MySQL versions do not.

Rule of thumb: use DENSE_RANK() when the business meaning is “level” or “tier,” not “position in a list.”

Real-world story

Imagine a marketplace analytics service that gives badges to sellers: Gold, Silver, and Bronze. The product team wants the badge logic to treat equal weekly revenue as the same tier, and they do not want missing tiers just because two sellers tied at the top.

The engineer uses DENSE_RANK() over weekly revenue. If two sellers both earn $10,000, they both get rank 1, and the next seller at $9,200 gets rank 2. That makes the badge rules easy: rank 1 = Gold, rank 2 = Silver, rank 3 = Bronze.

What goes wrong when misunderstood: someone swaps in RANK() and assumes the numbers will stay compact. Now two tied first-place sellers cause the next seller to jump to rank 3, so the Bronze tier disappears for that week. In production, support starts seeing complaints like “Why did my badge vanish when sales were still strong?” Logs show the job assigning rank 3 where the business expected rank 2, and the dashboard suddenly has empty tier slots. This is a classic bug because the data is correct, but the ranking function does not match the business rule.

Follow-up & Tricky Questions:

• How is DENSE_RANK() different from RANK()? RANK() leaves gaps after ties; DENSE_RANK() does not. If you need compact tiers, dense rank is the correct choice.
• How is it different from ROW_NUMBER()? ROW_NUMBER() gives every row a unique number, even when values tie. It is for sequencing rows, not ranking equal values together.
• Can I use PARTITION BY with it? Yes. PARTITION BY restarts the ranking for each group, such as ranking sales within each region instead of across the whole company.
• Can I filter by DENSE_RANK() in WHERE? Not directly in standard SQL, because window functions are evaluated after WHERE. Put the rank in a subquery or CTE, then filter the outer query.
• What happens with NULL values? They are ranked according to the database’s sort rules and any explicit NULLS FIRST/LAST option. The exact position can differ across engines, so be explicit when NULL matters.
• Do I always need ORDER BY? For ranking functions, yes in practice and by standard design: the order defines the meaning of the rank. Without it, there is no sensible ranking.
• Does it remove duplicates? No. It only assigns the same rank to equal values; all original rows still appear in the output.
• Is DENSE_RANK() faster than RANK()? Usually not in a meaningful way. They both need the same ordered input; the main cost is sorting, not the rank calculation itself.

Tricky gotchas:

• Can I use DENSE_RANK() to get exactly 3 rows? Not safely. It gives 3 distinct rank levels, which may return more than 3 rows when ties exist.
• If two rows tie, does the next row get rank 3 or 2? With DENSE_RANK(), it gets rank 2. The missing numbers only happen in RANK().
• Is it the same as counting distinct values? No. It is related, but not identical to COUNT(DISTINCT ...); DENSE_RANK() is row-by-row and depends on sort order and partitioning.

Common Mistakes:

• Using RANK() when you want compact tiers. Correction: use DENSE_RANK() if gaps would break the business meaning.
• Expecting one row per rank. Correction: ties share the same rank, so a rank can have many rows.
• Filtering in WHERE directly. Correction: compute the rank in a subquery or CTE, then filter outside.
• Forgetting that ORDER BY defines the result. Correction: always make the sort rule explicit, including tie-breakers if the business needs them.

Memory Hook: “Dense means packed stairs.” Every new distinct value is the next step, and equal values stand on the same step.

Cheat Sheet:

• DENSE_RANK() = same rank for ties, no gaps.
• RANK() = same rank for ties, gaps after ties.
• ROW_NUMBER() = unique sequence for every row.
• PARTITION BY restarts ranking per group.
• Use a CTE/subquery to filter by rank.
• Big cost is the sort, not the rank math.

Practice Tasks:

• Write a query that ranks employees by salary within each department using DENSE_RANK().
• Modify it so you return only the top 2 salary tiers per department, keeping all ties.
• Compare the output of ROW_NUMBER(), RANK(), and DENSE_RANK() on a dataset with repeated scores.