SQL, data modeling, data quality, EDA, visualization · insight-1037
Choose ROW_NUMBER, RANK or DENSE_RANK by deciding what a tie should do
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- aviation, retail, cross-industry
Summary: situation, action, reason, boundary
Situation
A report asks for the top 3 routes per month by revenue, and two routes tie for third place in some months.
Action
Decide the business rule first: exactly N rows (ROW_NUMBER with an explicit tie-break column), everyone who ties (RANK), or the N best distinct values (DENSE_RANK). Write the rule in the query comment and add a deterministic secondary ORDER BY.
Reason
Peers get the same rank value; ROW_NUMBER numbers physical rows, RANK leaves gaps after ties and DENSE_RANK counts peer groups.
Boundary
Top-N per group, leaderboards, deduplication by rank and cutoff-based flags.
Symptom or risk
ROW_NUMBER breaks ties arbitrarily, so reruns can return different routes, while RANK can return four rows for "top 3" without anyone planning for it.
Exceptions
Continuous measures with ties of practically zero probability, though rounded values make ties likely.
Trade-offs
An explicit tie-break column makes the answer stable but hides the fact that two entities were equal.
Pitfalls
Filtering rank <= 3 and then joining the result to a table with several rows per route.
Lessons and concepts
Claims and their sources
In PostgreSQL row_number numbers rows within the partition, rank returns the row number of the first row of the peer group (with gaps) and dense_rank counts peer groups without gaps; peers share the same rank.