RANK and DENSE_RANK

The RANK() and DENSE_RANK() functions assign a rank number to rows within a group.

These functions are commonly used to order data by progress, score, price, or other metrics.

They are window functions that operate with the OVER() clause.

Syntax

RANK and DENSE_RANK syntax

SELECT user_name,
       RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS dense_rank
FROM course_progress;

• PARTITION BY groups rows within each course
• ORDER BY defines the order used to assign rankings

Example: Ranking Course Completion

We have user progress for two courses, SQL Basics and Python Intro.

Each user has a completion_rate from 0 to 100, and we want to rank them within each course based on that value.

We can use RANK() and DENSE_RANK() to compare each user to others in the same course.

Ranking users by completion rate

SELECT user_name,
       course_name,
       completion_rate,
       RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS dense_rank
FROM course_progress;

Output:

user_name	course_name	completion_rate	rank	dense_rank
Heidi	Python Intro	92	1	1
Frank	Python Intro	90	2	2
Grace	Python Intro	90	2	2
Eve	Python Intro	85	4	3
Bob	SQL Basics	95	1	1
Charlie	SQL Basics	95	1	1
Alice	SQL Basics	92	3	2
Diana	SQL Basics	90	4	3

When should I use RANK vs DENSE_RANK?

• RANK() assigns the same rank to ties but leaves gaps afterward (e.g., 1 → 1 → 3).
• DENSE_RANK() also assigns the same rank to ties but without gaps (e.g., 1 → 1 → 2).

Use RANK() if the gap matters (like in competitions), and DENSE_RANK() if you want continuous rankings for reporting or analysis.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In