RANK vs DENSE_RANK vs ROW_NUMBER in SQL

The top-3 query that returns four rows

You ask for the top 3 earners in a department. You rank by salary, filter on rnk <= 3, and get four rows back.

Nothing is broken. Carla and the new hire, Nora, both earn $105,000, so both land on rank 3, and rnk <= 3 matches both of them. This is the part every RANK-vs-ROW_NUMBER comparison shows you the numbering for but skips the consequence of: RANK and DENSE_RANK can both let more rows through a "top N" filter than N, because a tie at the cutoff doesn't get resolved, it gets duplicated. Only ROW_NUMBER guarantees exactly N rows, and it buys that guarantee by picking an arbitrary winner between Carla and Nora, which is its own problem, covered further down.

The three numbering rules, side by side

Run all three functions over the same tied data and the difference in rules is easy to see in one table.

Six rows, one tie at $105,000. rnk reads 1, 2, 3, 3, 5, 6: the tied pair both get 3, and the next rank jumps to 5, because rank counts position, and two rows are occupying position 3 and 4 at once. dense_rnk reads 1, 2, 3, 3, 4, 5: the tie is still shared, but the next value is just "one more than the last distinct rank," so nothing gets skipped. rn reads 1 through 6 with no repeats at all, because ROW_NUMBER doesn't rank, it just counts, and a count can't tie.

ROW_NUMBER doesn't fix the tie, it hides it

Swap RANK for ROW_NUMBER in the original top-3 query and you do get exactly three rows back:

Exactly three, as promised. But look at which one of Carla and Nora made the cut. ORDER BY salary DESC only constrains the order by salary; it says nothing about what happens when two rows have the same salary, so the SQL standard leaves the tie-break unspecified. The engine is free to hand rank 3 to whichever of the tied rows it happens to scan first, and that choice isn't guaranteed to be the same every time the query plan changes, even though the ORDER BY looks complete to a human reading it.

Making the tie-break explicit

The fix isn't a different window function, it's a fuller ORDER BY. Add a second column that always differs between tied rows, and the pick becomes reproducible instead of accidental:

, first_name ASC breaks the $105,000 tie alphabetically, so Carla wins rank 3 every time this runs, on any engine, regardless of scan order. Whether alphabetical is the right tie-break depends on the report (hire date for "whoever's been here longest," employee ID for "whoever was hired first"), but some explicit column has to be there. The same fix applies to RANK and DENSE_RANK too, it won't stop them from returning extra rows at a genuine tie, but it makes which rows tie, and in what order they print, deterministic rather than a side effect of how the engine happened to read the table.

Picking the right one

• ROW_NUMBER: you need exactly N rows, no more, no less, deduplicating or paginating. Add a tie-break column or accept that ties resolve arbitrarily.
• RANK: you're showing people their position, like a leaderboard, and two people tied for 2nd should both see "2nd," with 3rd left empty rather than reused.
• DENSE_RANK: you care about how many distinct tiers exist, like "how many different salary levels are there in this department," where a gap in the numbering would overstate how many tiers there really are.

Where to go next

The RANK() per-group example, DENSE_RANK() example, and ROW_NUMBER() example each walk the single function in more depth with its own variations. For a worked case where DENSE_RANK is the right tool because a plain MAX subquery and a LIMIT/OFFSET approach both handle ties differently, see how do you find the second highest salary in SQL. The practice problems are a good place to try writing a top-N query with a deliberate tie in the data and confirming your ORDER BY picks the row you meant it to.