RANK

Why interviewers love RANK(): it checks whether you know the difference between ordering rows and numbering them when ties appear.

Question: What does RANK() do in SQL?

Answer: RANK() is a window function that assigns a position to each row after sorting it. If two rows have the same sort value, they share the same rank. The next row then skips ahead, so ties create gaps.

Interview-Ready Answer: I use RANK() when I need ordered results but want equal values to share the same position. It’s a window function, so it keeps every row and calculates the rank over a sorted set, often within each group using PARTITION BY. The key detail is that ties create gaps: if two rows tie for 1st, the next row is 3rd. If I want no gaps, I’d use DENSE_RANK(); if I want unique row numbers, I’d use ROW_NUMBER().

What RANK() is

A window function is a function that computes a value across a related set of rows without collapsing them the way GROUP BY does. RANK() belongs to the ranking family: it tells you where a row stands inside an ordered list, and rows with the same ordering value are treated as peers (rows that compare equal for the window ORDER BY).

How it works under the hood

1. The database forms the window: the full result set, or each group if you use PARTITION BY.
2. It sorts rows by the window ORDER BY clause. This sort is the expensive part.
3. It scans the sorted rows in order and compares each row with the previous peer group.
4. If the current row has the same ordering values as the previous row, it gets the same rank.
5. If the ordering values change, the rank jumps by the size of the previous peer group, which is why gaps appear.
6. The final output still contains every original row; nothing is collapsed or aggregated away.

When to use it

• Leaderboards where ties should share a place.
• Reports like “top sellers per region” where equal sales should be treated fairly.
• Any time the business meaning of a tie matters more than unique numbering.

RANK() vs the other ranking functions

Performance and edge cases

Ranking usually costs about the same as a sort: roughly O(n log n) for the sort plus O(n) to walk the rows. If you partition first, the cost becomes the sum of each partition’s sort cost. Large partitions can spill to disk if the engine’s memory grant is too small, which is when a query that looked fine in dev starts taking seconds in production.

• If you want equal values to share a rank, do not add a tie-breaker column inside the window ORDER BY; that changes the definition of “equal.”
• Window functions are usually evaluated after WHERE, so you filter ranks in a subquery or CTE.
• Without a meaningful window ORDER BY, ranking is either not allowed or not useful, depending on the database.

Real-world story

Imagine a sales dashboard for a subscription company. Every month, the business wants the top 10 reps per region, and if two reps tie on revenue, they should share the same place on the leaderboard.

Using RANK() lets the dashboard show fair standings: two reps tied at $50,000 both get rank 1, and the next rep gets rank 3. That is exactly the kind of detail executives notice.

What goes wrong: a developer swaps in ROW_NUMBER() because it “also numbers rows.” Suddenly tied reps get different positions, one rep appears to beat another even though their numbers are equal, and the monthly report no longer matches finance. The symptom is a support ticket like “why did the leaderboard change when sales did not?” and the bug shows up as conflicting totals between the dashboard and the exported report.

Follow-up & Tricky Questions:

• How is RANK() different from DENSE_RANK()? Both give the same rank to ties, but RANK() leaves gaps and DENSE_RANK() does not. If the next position after a tie must be consecutive, use DENSE_RANK().
• How do you get the top 3 rows per group? Put RANK() in a CTE or subquery, then filter WHERE rank <= 3. That keeps the window logic separate from the filter step.
• Can you use RANK() in WHERE? Usually no, because WHERE runs before window functions are computed. Use a subquery, CTE, or a database feature like QUALIFY if your engine supports it.
• What does PARTITION BY change? It restarts ranking for each group, such as each department or each month. Without it, the ranking covers the whole result set.
• Does RANK() guarantee a unique order? No. Ties intentionally share the same rank, so if you need a single winner, use ROW_NUMBER() or add a separate business rule.
• Tricky: if I add a tie-breaker column to the window ORDER BY, do ties still share a rank? Not usually. The extra sort column becomes part of the ranking key, so rows that used to tie may no longer be peers.
• Tricky: is RANK() zero-based? No, ranking starts at 1. The first row in each partition gets rank 1.
• Tricky: why does the next rank jump from 1 to 3? Because two rows shared rank 1, so rank 2 was skipped. That is the defining feature of RANK().

Common Mistakes:

• Using ROW_NUMBER() when ties should share a place. Fix: use RANK() or DENSE_RANK() depending on whether you want gaps.
• Filtering rank directly in WHERE. Fix: compute the rank in a subquery or CTE, then filter outside it.
• Adding a random tie-breaker inside the window ORDER BY. Fix: only rank by the business metric if equal values must stay tied.
• Forgetting PARTITION BY when you need per-group ranking. Fix: partition by the category, department, or date bucket you want to restart within.

Memory Hook: Think of a podium: tied athletes share the same medal, and the next place is skipped. RANK() is the “shared medal, skipped seat” rule.

Cheat Sheet:

• RANK() = same value, same rank.
• Ties create gaps.
• Use PARTITION BY to rank inside groups.
• Filter ranks in a CTE or subquery, not WHERE.
• Use DENSE_RANK() for no gaps, ROW_NUMBER() for unique numbering.

Practice Tasks:

• Rank products by revenue inside each category.
• Return the top 3 salaries per department, including ties.
• Rewrite the same query three times using RANK(), DENSE_RANK(), and ROW_NUMBER() to see the difference.