What is the difference between RANK, DENSE_RANK, and ROW_NUMBER when there are tied values in the ordering column?

Intermediate

Answer

ROW_NUMBER always assigns a unique, sequential number even to tied rows, RANK assigns the same rank to tied rows but then skips subsequent rank numbers to account for the tie, while DENSE_RANK also assigns the same rank to tied rows but does not skip any subsequent numbers, keeping the ranking sequence tightly consecutive.

SELECT Name, Score,
  ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,
  RANK() OVER (ORDER BY Score DESC) AS Rank,
  DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Scores;

Real-world example A leaderboard ranking system uses DENSE_RANK to properly display tied scores with the same rank without any gaps, giving a cleaner presentation than RANK would produce for their specific use case.

Common follow-ups: Which of these three functions is most appropriate for a typical leaderboard style ranking?;How do you decide which one is correct for a specific reporting requirement?

Aggregate Functions & GROUP BY;Data Types & Schema Design

All 7 Window Functions questions