RANK vs DENSE_RANK vs ROW_NUMBER in SQL
All three number rows in order. They only disagree about ties, and that's exactly what interview questions test.
How each handles a tie
ROW_NUMBER never ties: tied rows get different numbers (1, 2, 3, 4), in an order you don't control unless you add a tie-breaker. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number and doesn't skip (1, 2, 2, 3).
Which one to use
'Top 3 distinct salaries' or 'third-highest salary' means DENSE_RANK. 'Exactly one row per group', like each customer's latest order, means ROW_NUMBER with a tie-breaker. RANK fits competition-style standings where a tie should push later places down.
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- ROW_NUMBER for a 'top N salaries' question: tied people get split up.
- RANK for 'Nth highest': after a tie, rank N may not exist.
- ROW_NUMBER without a tie-breaker, which picks a random row among ties.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- Top 3 earners per departmentWindow functions · DENSE_RANK()Free
- Each customer's first orderDISTINCT ON · first row per groupPro
- Third-highest salary per departmentNth highest · DENSE_RANK() vs ROW_NUMBER()Pro
- Largest charge per accountROW_NUMBER() · deterministic tie-breakerPro
- First vs latest order valueFIRST_VALUE · LAST_VALUE · frame trapPro
- Current plan per userDISTINCT ON · latest row per keyPro
- First-touch vs last-touch attributionROW_NUMBER both ways · attributionPro