SQLRepGuidesPlaygroundLearning path

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.

try it

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.