RANK( ), DENSE_RANK( ), and ROW_NUMBER( ): How Each Handles Ties

RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same numbers until your data has a tie. Where they differ is how they treat tied values. SQL Server has a fourth ranking function, NTILE, which splits rows into groups instead of ranking them.

The Sample Table

Eight teas have a score each. Two teas tie at 9.5, and three tie at 8.7. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE TeaScores (Tea VARCHAR(20) NOT NULL, Score DECIMAL(3,1) NOT NULL);
INSERT INTO TeaScores (Tea, Score) VALUES
  ('Darjeeling', 9.8), ('Assam', 9.5), ('Nilgiri', 9.5), ('Oolong', 9.1),
  ('Sencha', 8.7), ('Matcha', 8.7), ('Rooibos', 8.7), ('Chamomile', 8.0);
GO

Three Functions, One Query

Each ranking function reads its order from the ORDER BY inside its OVER( ) clause. Put all three in one query, and you can compare them row by row.

SELECT Tea, Score,
  RANK() OVER (ORDER BY Score DESC) AS RankNo,
  DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRankNo,
  ROW_NUMBER() OVER (ORDER BY Score DESC, Tea) AS RowNo
FROM TeaScores
ORDER BY Score DESC, Tea;

Until the first tie, the three columns match. Assam and Nilgiri tie at 9.5, and RANK and DENSE_RANK both give them 2. After that, the functions part ways.

RANK leaves a gap after a tie. Two teas share 2, so no row gets 3, and the next row gets 4. The three teas at 8.7 share 5, so the next rank is 8. DENSE_RANK has no gaps: Oolong is 3, the 8.7 teas share 4, and Chamomile is 5.

Here’s a simple way to remember them. RANK is one plus the number of rows that score higher. DENSE_RANK is one plus the number of distinct scores that are higher. Check Oolong: three rows score higher, so its RANK is 4. Only two distinct scores are higher, so its DENSE_RANK is 3.

ROW_NUMBER never repeats, so it gives tied rows different numbers. Nothing decides their order unless you add a tiebreaker. The query above uses Tea as the tiebreaker. Matcha, Rooibos and Sencha always come out as 5, 6 and 7.

The ORDER BY Inside OVER

The ORDER BY inside OVER( ) sets the numbers. The ORDER BY at the end of the query only sets the order of the rows you see. This query numbers the teas by score and shows them by name.

SELECT Tea, Score, ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNo FROM TeaScores ORDER BY Tea;

Without a tiebreaker, the three teas at 8.7 came out as Sencha 5, Matcha 6 and Rooibos 7. The earlier query numbered them Matcha, Rooibos and Sencha. Same data, different numbers, so add the tiebreaker.

When OVER Is Missing

Every ranking function needs an OVER( ) clause, and SQL Server tells you when it’s missing. The second query below has an empty OVER( ) clause.

SELECT Tea, Score, RANK() FROM TeaScores ORDER BY Score DESC;
GO

SELECT Tea, Score, RANK() OVER () FROM TeaScores ORDER BY Score DESC;

The first query fails with message 10753, which says the function must have an OVER clause. The second fails with message 4112, which says the OVER clause needs an ORDER BY. Move the ORDER BY inside the parentheses.

The Top Three Distinct Scores

DENSE_RANK is the one to use for the top three distinct scores, because it has no gaps. Rank the rows in a CTE, then filter on the number. The query below runs the same filter with all three functions.

WITH Ranked AS (
  SELECT Tea, Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS ScoreRating
  FROM TeaScores
)
SELECT Tea, Score, ScoreRating FROM Ranked WHERE ScoreRating <= 3 ORDER BY ScoreRating, Tea;

WITH Ranked AS (
  SELECT Tea, Score, RANK() OVER (ORDER BY Score DESC) AS ScoreRating
  FROM TeaScores
)
SELECT Tea, Score, ScoreRating FROM Ranked WHERE ScoreRating <= 3 ORDER BY ScoreRating, Tea;

WITH Ranked AS (
  SELECT Tea, Score, ROW_NUMBER() OVER (ORDER BY Score DESC, Tea) AS ScoreRating
  FROM TeaScores
)
SELECT Tea, Score, ScoreRating FROM Ranked WHERE ScoreRating <= 3 ORDER BY ScoreRating, Tea;

DENSE_RANK returns four rows, and they hold the three top scores: 9.8, 9.5 and 9.1. RANK returns three rows and misses Oolong, because Oolong has rank 4. ROW_NUMBER returns exactly three rows, one per number.

Use <= and not =. Filtering on = 3 returns only Oolong, the tea at third place. To list the scores alone, add DISTINCT.

WITH Ranked AS (
  SELECT Tea, Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS ScoreRating
  FROM TeaScores
)
SELECT DISTINCT Score FROM Ranked WHERE ScoreRating <= 3 ORDER BY Score DESC;

Clean Up

Remove the sample table when you finish.

DROP TABLE TeaScores;

What to Remember

Use RANK when ties should share a place and the gaps are fine. Use DENSE_RANK to count distinct values, such as the top three scores. Use ROW_NUMBER when every row needs its own number, and add a tiebreaker to its ORDER BY.

To go further, read RANK vs DENSE_RANK: Keep Poll Ties at the Cutoff and Splitting Work Into Equal-Sized Batches With NTILE.

A ranking function is not a sort order, it is a rule for what a tie is worth.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.