RANK and DENSE_RANK difference in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the RANK() function do in SQL?

RANK() assigns a rank number to each row within a partition of a result set, with gaps in ranking when there are ties.

beginner

How does DENSE_RANK() differ from RANK() in SQL?

DENSE_RANK() assigns ranks without gaps, so tied rows get the same rank and the next rank is the immediate next number.

intermediate

If two rows tie for rank 2, what rank will the next row get with RANK()?

The next row will get rank 4, because RANK() leaves gaps after ties.

intermediate

If two rows tie for rank 2, what rank will the next row get with DENSE_RANK()?

The next row will get rank 3, because DENSE_RANK() does not leave gaps after ties.

intermediate

Why might you choose DENSE_RANK() over RANK()?

Choose DENSE_RANK() when you want continuous ranking numbers without gaps, useful for compact ranking lists.

What happens to the rank numbers after a tie when using RANK()?

A They skip numbers creating gaps

B They assign the same rank to all rows

C They reset to 1

D They continue without gaps

RANK() skips rank numbers after ties, creating gaps.

Which function assigns ranks without gaps after ties?

A COUNT()

B ROW_NUMBER()

C RANK()

D DENSE_RANK()

DENSE_RANK() assigns ranks without gaps after ties.

If two rows tie for first place, what rank does the next row get with DENSE_RANK()?

A 3

B 2

C 1

D 4

DENSE_RANK() assigns the next rank as 2 after two tied rows at rank 1.

Which ranking function can produce gaps in the ranking sequence?

A RANK()

B DENSE_RANK()

C ROW_NUMBER()

D SUM()

RANK() produces gaps in ranking numbers after ties.

What is a key difference between RANK() and DENSE_RANK()?

A RANK() assigns unique ranks, DENSE_RANK() does not

B DENSE_RANK() leaves gaps, RANK() does not

C RANK() leaves gaps after ties, DENSE_RANK() does not

D Both assign ranks randomly

RANK() leaves gaps after ties; DENSE_RANK() assigns consecutive ranks without gaps.

Explain how RANK() and DENSE_RANK() handle ties differently in SQL.

Think about what happens to the next rank number after a tie.

Describe a scenario where you would prefer to use DENSE_RANK() instead of RANK().

Consider when gaps in ranking numbers might confuse users.