ROW_NUMBER vs RANK vs DENSE_RANK: Ties and Top N per Group

ROW_NUMBER gives each row a unique position. RANK gives ties the same rank and leaves gaps. DENSE_RANK gives ties the same rank without gaps. Your choice determines what “top two” means when scores are equal.

Function	Positions for scores 95, 95, 80	Best fit
ROW_NUMBER	1, 2, 3 with an explicit tie-breaker	Up to N rows per group
RANK	1, 1, 3	Competition ranking
DENSE_RANK	1, 1, 2	Top N distinct score levels

PARTITION BY team restarts the numbering for each team. Unlike GROUP BY, window functions retain the individual rows. You can show a person’s score and their position in the same result.

Try it yourself in SQLite

Open SQL Lab, paste this setup, and run it once. Then run each query separately. Table and column names stay in English so the same SQL works in every language. The lab interface and linked lessons are currently in English.

CREATE TABLE blog_scores (
  id INTEGER PRIMARY KEY, team TEXT, name TEXT, score INTEGER
);
INSERT INTO blog_scores VALUES
  (1, 'A', 'Asha', 95), (2, 'A', 'Luis', 95), (3, 'A', 'Mei', 80),
  (4, 'B', 'Noor', 90), (5, 'B', 'Omar', 70), (6, 'B', 'Pia', 70);

1. Compare all three functions on tied scores

The ROW_NUMBER ordering includes id, so equal scores are resolved consistently. RANK and DENSE_RANK order only by score, so equal scores remain peers. Adding id to their window ORDER BY would break those ties and defeat this comparison. The final ORDER BY controls display order, separately from the window calculations.

SELECT team, name, score,
  ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS row_num,
  RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_num
FROM blog_scores
ORDER BY team, score DESC, id;

Expected result

team	name	score	row_num	rank_num	dense_num
A	Asha	95	1	1	1
A	Luis	95	2	1	1
A	Mei	80	3	3	2
B	Noor	90	1	1	1
B	Omar	70	2	2	2
B	Pia	70	3	2	2

2. Return up to two people per team

Use ROW_NUMBER and filter the calculated position in an outer query. Team B keeps Noor and Omar; Pia is excluded because id resolves the tie. Each team has at most two rows. SQLite does not allow this window result in WHERE at the same query level, so the CTE creates a filterable intermediate result.

WITH ranked AS (
  SELECT team, name, score, id,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE position <= 2
ORDER BY team, position;

Expected result

team	name	score
A	Asha	95
A	Luis	95
B	Noor	90
B	Omar	70

3. Return the top two distinct score levels

DENSE_RANK keeps every person in the highest two score bands. Both teams return three people in this dataset. This is appropriate when the rule says to include everyone tied at qualifying score levels, but it is not an exact two-person limit.

WITH ranked AS (
  SELECT team, name, score, id,
    DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS score_band
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE score_band <= 2
ORDER BY team, score DESC, id;

Expected result

team	name	score
A	Asha	95
A	Luis	95
A	Mei	80
B	Noor	90
B	Omar	70
B	Pia	70

Practice and check your answer

Replace DENSE_RANK with RANK in the last query and keep score_band <= 2. Team A now returns only Asha and Luis: Mei has rank 3 because the two tied leaders occupy the first two positions. Team B still returns Noor, Omar, and Pia.

Before choosing a function, ask whether the requirement is “two people,” “competition ranks up to two,” or “two distinct scores.” These are three different business rules. Test ties explicitly; a dataset with unique scores cannot reveal the difference.

Use a unique, stable tie-breaker for ROW_NUMBER whenever you need reproducible selections. Without one, the database can choose a different order among equal scores. If null scores are possible, decide whether they qualify and define their ordering or filter them out; NULL ordering varies across engines.

For deduplication, partition by the duplicate key and order by the preferred timestamp plus a unique ID. Keep position 1 in an outer query, but first inspect the discarded rows. Ranking rows is a selection technique; it does not prove that deleting them is safe.

Documentation and next steps

Practice the ranking lesson and OVER clause lesson. These queries run in SQLite; PostgreSQL and MySQL 8 also support these ranking functions. Dialect features such as QUALIFY are not assumed here.

• SQLite reference
• Database reference