In a top-3-per-group query, how does RANK() differ from ROW_NUMBER() when the metric ties?

## Three functions, one filter Every top-N-per-group query ends in the same predicate — `WHERE rank_column <= N` — but the number of rows that come back depends entirely on which ranking function produced the column. The three candidates differ only in how they treat rows whose window `ORDER BY` values are equal. Take one department with salaries 500, 400, 300, 300 and 200: ```sql SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk FROM employees; -- rn = 1, 2, 3, 4, 5 -- rnk = 1, 2, 3, 3, 5 -- drnk = 1, 2, 3, 3, 4 ``` Filter each column with `<= 3` and you get three different answers: `rn` keeps three rows, `rnk` keeps four (both rows tied at 3), and `drnk` also keeps four here — but with salaries 500, 400, 400, 400, 300 the dense ranks are 1, 2, 2, 2, 3 and `drnk <= 3` keeps all five rows. ## What each one means in business terms **`ROW_NUMBER()`** answers "give me at most N rows per group". It is the right choice when a downstream consumer needs a fixed-size list — a three-slot leaderboard tile, a paging window, a one-row-per-key export. Its cost is arbitrariness: when the metric ties, SQL gives no rule for who wins, so the query can return different rows on different runs. **`RANK()`** answers "give me the top N places, and if the last place is shared, include everyone in it". This is competition ranking: two silver medals and no bronze. It is the honest choice when excluding a tied row would be unfair or wrong, and the price is that the row count is not fixed — the caller must tolerate more than N rows. **`DENSE_RANK()`** answers "give me every row whose metric is among the top N distinct values". Use it for "the three highest salary bands" or "everything from the top three price points". The row count is even less bounded, since a single popular value can carry hundreds of rows. ## Making ROW_NUMBER deterministic If you pick `ROW_NUMBER()` because you need exactly N rows, add a unique column to the window `ORDER BY` so the tie is broken by the query rather than by whatever order rows happen to arrive in: ```sql ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) AS rn ``` Now two employees on the same salary are ordered by `employee_id`, and the query returns the same three rows every time. Without such a tiebreaker the result is unspecified, and "it looked stable in testing" is not a guarantee. ## The diagnostic version of this question Interviewers often approach it from the failure side: "your latest-order-per-customer query returns 8,140 rows but you only have 8,000 customers — why?" The usual cause is `RANK() = 1` (or `DENSE_RANK() = 1`) where `ROW_NUMBER() = 1` was intended: any customer with two orders sharing the top timestamp contributes two rows. Swapping to `ROW_NUMBER()` with a tiebreaker collapses each customer back to a single row. The mirror-image bug is using `ROW_NUMBER()` for a leaderboard and quietly dropping a genuinely tied competitor. ## A caution about the window ORDER BY itself All of this assumes there is a window `ORDER BY`. `ROW_NUMBER() OVER (PARTITION BY department_id)` with no ordering is syntactically legal and numbers the rows in an unspecified order, which turns "top 3" into "any 3" — a bug that will not raise an error and may not show up until the data grows. Similarly, if the metric is nullable, engines differ on whether NULLs sort first or last by default, so a NULL can occupy rank 1 under `DESC`; write `NULLS LAST` explicitly (or exclude NULL metrics in the inner query) when it matters. ## How to answer in an interview State the mechanical difference in one sentence, then immediately tie it to the row count the outer filter produces, and finish by asking which behaviour the requirement wants. That last step is what separates a candidate who memorised three definitions from one who has shipped the query.