RANK vs DENSE_RANK in SQL: Ties, Gaps and a Worked Score Example

Two students score 90. The next scores are 80 and 70. Should their positions be 1, 1, 3, 4 or 1, 1, 2, 3? SQL gives you both choices: RANK() leaves gaps after a tie, while DENSE_RANK() does not.

This short note explains the SQL ranking example used in TechVani's lesson. You can run the full example below without guessing the output. The queries were checked with SQLite.

The difference in one table

Student	Score	RANK	DENSE_RANK
Asha	90	1	1
Bilal	90	1	1
Charu	80	3	2
Dev	70	4	3

RANK: Asha and Bilal share first place. Since two rows occupy that place, Charu gets rank 3. Rank 2 is skipped. Dev gets rank 4.

DENSE_RANK: 90 is the first distinct score, 80 is the second and 70 is the third. Equal scores share a position, but the next distinct score always advances by one.

Run the example

Create the table and insert these four rows:

CREATE TABLE scores (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  score INTEGER NOT NULL
);
INSERT INTO scores VALUES
  (1, 'Asha', 90),
  (2, 'Bilal', 90),
  (3, 'Charu', 80),
  (4, 'Dev', 70);

Then compare the two functions in the same query:

SELECT student_name, score,
       RANK() OVER (ORDER BY score DESC) AS rank_no,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_no
FROM scores
ORDER BY score DESC, student_id;

The OVER clause makes these window functions. They add a result to each row instead of collapsing the rows into a single grouped result. ORDER BY score DESC inside the window means the highest score comes first.

The final ORDER BY controls how the output is displayed. It sorts tied students by their ID, while leaving the ranking calculation based only on score.

Do not accidentally remove ties

This change looks harmless, but changes the question:

RANK() OVER (ORDER BY score DESC, student_id)

Now students with the same score but different IDs are not peers: SQL compares both columns. The two 90s receive separate ranks. Keep only the columns that define a tie inside the ranking window. Use an outer ordering column when you only want stable display order.

Second highest distinct score

Use dense rank when you want the second score level, including every student tied at that score. Compute the rank in a CTE, then filter it in the outer query:

WITH ranked AS (
  SELECT student_name, score,
         DENSE_RANK() OVER (ORDER BY score DESC) AS position
  FROM scores
)
SELECT student_name, score
FROM ranked
WHERE position = 2;

The result here is Charu with score 80. If another student also had 80, that student would be returned too. Filtering RANK() = 2 would return no rows in the original example because rank 2 does not exist.

What about ROW_NUMBER and PARTITION BY?

ROW_NUMBER() assigns one different number to every row. Use a tie-breaker when the choice between equal scores matters, for example ROW_NUMBER() OVER (ORDER BY score DESC, student_id). This answers "which individual row is next?", not "which score position is next?".

PARTITION BY class_id, when your table has a class ID, restarts the ranking separately for each class. Without it, all rows in this query belong to one ranking group.

Ranking decision map: rows or score levels?

What must the result count?
↓
Every individual row → ROW_NUMBER, with a stable tie-breaker
Competition positions, gaps after ties → RANK
Distinct score levels, no gaps → DENSE_RANK
↓
Restart for each class/team → add PARTITION BY
↓
Filter computed positions → CTE/subquery, then outer WHERE

The ranking order and display order answer different questions. A window ORDER BY defines peers. The outer ORDER BY makes the displayed result stable. Neither RANK nor DENSE_RANK chooses one winner from a tie.

Which function fits the question?

Need	Function/filter	Tie consequence
Exactly two rows	ROW_NUMBER ≤2, stable tie-breaker	May split a tied score group
Top two competition positions	RANK ≤2	On our data returns only the two90s
Top two distinct score levels	DENSE_RANK ≤2	Returns90s and80, three rows
Second highest distinct score	DENSE_RANK =2	Returns every row at that score
One chosen row per class	ROW_NUMBER partitioned by class	Define a meaningful tie-breaker

Three functions side by side

SELECT student_name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, student_id) AS row_no,
       RANK() OVER (ORDER BY score DESC) AS rank_no,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_no
FROM scores
ORDER BY score DESC, student_id;

Tested SQLite result, in query order:

Asha | 90 | 1 | 1 | 1
Bilal | 90 | 2 | 1 | 1
Charu | 80 | 3 | 3 | 2
Dev | 70 | 4 | 4 | 3

Top two score levels, including ties

WITH ranked AS (
  SELECT student_id, student_name, score,
         DENSE_RANK() OVER (ORDER BY score DESC) AS level_no
  FROM scores
)
SELECT student_name, score
FROM ranked
WHERE level_no <= 2
ORDER BY score DESC, student_id;

Tested SQLite result, in query order:

Asha | 90
Bilal | 90
Charu | 80

Restart ranking within each class

WITH class_scores(class_id, student_id, name, score) AS (
  VALUES ('A',1,'Asha',90), ('A',2,'Bilal',90), ('A',3,'Charu',80),
         ('B',4,'Dev',70), ('B',5,'Eva',60)
)
SELECT class_id, name, score,
       RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_no
FROM class_scores
ORDER BY class_id, score DESC, student_id;

Tested SQLite result, in query order:

A | Asha | 90 | 1
A | Bilal | 90 | 1
A | Charu | 80 | 3
B | Dev | 70 | 1
B | Eva | 60 | 2

Practice answer: two tied groups

WITH marks(id, score) AS (VALUES (1,100),(2,100),(3,95),(4,95),(5,80))
SELECT score, RANK() OVER (ORDER BY score DESC),
       DENSE_RANK() OVER (ORDER BY score DESC)
FROM marks ORDER BY score DESC, id;

Tested SQLite result, in query order:

100 | 1 | 1
100 | 1 | 1
95 | 3 | 2
95 | 3 | 2
80 | 5 | 3

Exam answer: definitions and formulas

A peer group contains rows equal on every expression in the window ORDER BY. RANK is one plus the number of rows preceding the current peer group. DENSE_RANK is one plus the number of preceding peer groups. ROW_NUMBER assigns consecutive numbers to individual rows. These window functions keep individual result rows; GROUP BY aggregates normally form one result row per group.

Common mistakes to catch

• Adding ID inside RANK ordering: unique IDs break score ties. Add ID to outer ordering instead when ties are defined by score alone.
• Filtering window results in the same WHERE: use the demonstrated CTE or subquery. Some systems offer QUALIFY, but it is not portable to these SQLite/PostgreSQL examples.
• Assuming top-N means exactly N rows: ties can increase result count; missing competition positions can reduce the number of score levels.
• Unknown scores: these examples use NOT NULL. For nullable scores, define the policy before ranking. Do not silently replace an unknown score with zero; NULL ordering defaults vary across DBMSs.
• Assuming speed from syntax: performance depends on data, partitions and execution plan. No benchmark or index guarantee is implied.

End-topic questions with answers

1. For90,90,80,70, why is third-row RANK3? Two rows precede the80peer group, so1+2=3.
2. Why is its DENSE_RANK2? One distinct higher-score group precedes it, so1+1=2.
3. Can RANK2 return no rows? Yes. The tie at position1skips position2in our example.
4. What does PARTITION BY change? It creates separate ranking groups, each starting at1.
5. How to return exactly one highest-scoring student per class? ROW_NUMBER partitioned by class, ordered by score descending plus a chosen stable tie-breaker; filter row_no=1outside.
6. Does DENSE_RANK eliminate duplicate rows? No. Tied rows remain and share a rank.
7. Practice100,100,95,95,80? RANK1,1,3,3,5; DENSE_RANK1,1,2,2,3, verified in the query above.

Test scope: original setup and complete queries plus all four complete queries added here executed on SQLite. PostgreSQL semantics checked against its official documentation; no PostgreSQL runtime test is claimed. Run in a scratch database.

Quick check

For scores 100, 100, 95, 95, 80: RANK gives 1, 1, 3, 3, 5; DENSE_RANK gives 1, 1, 2, 2, 3. Count the rows before a score for RANK. Count the distinct higher scores for DENSE_RANK.

Related notes: SQL complete notes and SQL output questions on NULL, COUNT and LEFT JOIN.

Sources: PostgreSQL window functions and SQLite window functions. This explanation, data and practice example are original.