PrismitaLEARN · PRACTICE · GROW

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

All assign a number over an ordered window. ROW_NUMBER() — always unique (1,2,3,4), ties broken arbitrarily. RANK() — ties share a number, then it skips (1,1,3,4). DENSE_RANK() — ties share a number, no gap (1,1,2,3).

ROW_NUMBER gives a strict 1,2,3 with no duplicates — if two rows tie on the sort key it picks one arbitrarily, so I add a tiebreaker. RANK lets ties share the same rank and then leaves a gap — two firsts, then third. DENSE_RANK also lets ties share but doesn't leave a gap — two firsts, then second. For paging or removing duplicates I use ROW_NUMBER; for an actual leaderboard where ties should count equally I use one of the ranks.

  • Use ROW_NUMBER() for pagination and de-duplication (keep row 1 of each partition).
  • Use RANK()/DENSE_RANK() for leaderboards where ties genuinely tie.
  • “Top 3 products per category” with ties included → DENSE_RANK() ... <= 3.
  • Ordering must be deterministic or results vary between runs — add a tiebreaker column.
All SQL Server questions More on T-SQL