Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK0%

Positional Value Functions: LEAD and LAG

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Intermediate13 min read•Updated: 2026-09-12

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Ranking data is one of the most frequent tasks in business analytics: identifying the top 3 best-selling products per category, finding the 10 highest-earning salespeople per region, or assigning sequential numbers to user actions.

SQL provides three primary ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK(). While all three assign integers based on a specified ordering, they handle ties (identical values) in distinctly different ways.

The Three Ranking Functions Explained

1
ROW_NUMBER(): Assigns a strict sequential integer (1, 2, 3, 4...) to each row within a partition. It never produces ties and never skips numbers. If two rows have identical values, their ordering is arbitrary unless broken by secondary sort columns.
2
RANK(): Assigns identical rank numbers to rows with identical values (ties). However, it skips subsequent rank numbers to maintain position parity (e.g., 1, 2, 2, 4).
3
DENSE_RANK(): Assigns identical rank numbers to rows with identical values, but never skips numbers in the sequence (e.g., 1, 2, 2, 3).

Side-by-Side Visual Comparison

Suppose we rank 5 students by score in descending order:

Student	Score	`ROW_NUMBER()`	`RANK()`	`DENSE_RANK()`
Diana	100	1	1	1
Bruce	90	2	2	2
Clark	90	3	2 (Tie!)	2 (Tie!)
Arthur	80	4	4 (Skipped 3!)	3 (No skip!)
Barry	70	5	5	4

Notice:

ROW_NUMBER arbitrarily gave Bruce 2 and Clark 3.
RANK gave Bruce and Clark 2, then skipped to 4 for Arthur.
DENSE_RANK gave Bruce and Clark 2, then assigned Arthur 3 seamlessly.

The Classic "Top N Per Category" Problem

A frequent interview question and production problem is: "Find the top 2 highest-paid employees in every department."

Because window functions cannot be placed directly in a WHERE clause, we use a Common Table Expression (CTE):

1WITH RankedStaff AS (
  SELECT 
      emp_id,
      first_name,
      last_name,
      department,
      salary,
      DENSE_RANK() OVER (
          PARTITION BY department 
          ORDER BY salary DESC
      ) AS salary_rank
  FROM employees
13)
14SELECT 
  department,
  salary_rank,
  first_name,
  last_name,
  salary
20FROM RankedStaff
21WHERE salary_rank <= 2
22ORDER BY department, salary_rank;

Additional Ranking Functions: PERCENT_RANK & NTILE

MySQL 8.0 also includes:

NTILE(n): Distributes rows evenly into n roughly equal buckets (e.g., NTILE(4) divides customer spend into quartiles).
PERCENT_RANK(): Calculates relative rank as a percentage between 0.0 and 1.0 using the formula (rank - 1) / (total_rows - 1).

1-- Divide customers into spending quartiles (Top 25%, Upper-Mid, Lower-Mid, Bottom 25%)
2SELECT 
3    customer_id,
4    lifetime_spend,
5    NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS spend_quartile
6FROM customer_aggregates;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

If two rows tie for second place, what sequence will RANK() produce for the first four rows?

Question 2

Which ranking function guarantees no gaps in the numbering sequence even when ties occur?

Question 3

What does ROW_NUMBER() do when two rows have identical sorting values?

Question 4

How do you retrieve the top 3 highest revenue products per category?

Question 5

What does NTILE(4) accomplish on an ordered dataset?

Next Lesson

Positional Value Functions: LEAD and LAG

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
The OVER() Clause & PARTITION BY Mechanics	Positional Value Functions: LEAD and LAG

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Positional Value Functions: LEAD and LAG

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Intermediate13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

The Three Ranking Functions Explained

1
ROW_NUMBER(): Assigns a strict sequential integer (1, 2, 3, 4...) to each row within a partition. It never produces ties and never skips numbers. If two rows have identical values, their ordering is arbitrary unless broken by secondary sort columns.
2
RANK(): Assigns identical rank numbers to rows with identical values (ties). However, it skips subsequent rank numbers to maintain position parity (e.g., 1, 2, 2, 4).
3
DENSE_RANK(): Assigns identical rank numbers to rows with identical values, but never skips numbers in the sequence (e.g., 1, 2, 2, 3).

Side-by-Side Visual Comparison

Suppose we rank 5 students by score in descending order:

Student	Score	`ROW_NUMBER()`	`RANK()`	`DENSE_RANK()`
Diana	100	1	1	1
Bruce	90	2	2	2
Clark	90	3	2 (Tie!)	2 (Tie!)
Arthur	80	4	4 (Skipped 3!)	3 (No skip!)
Barry	70	5	5	4

Notice:

ROW_NUMBER arbitrarily gave Bruce 2 and Clark 3.
RANK gave Bruce and Clark 2, then skipped to 4 for Arthur.
DENSE_RANK gave Bruce and Clark 2, then assigned Arthur 3 seamlessly.

The Classic "Top N Per Category" Problem

A frequent interview question and production problem is: "Find the top 2 highest-paid employees in every department."

Because window functions cannot be placed directly in a WHERE clause, we use a Common Table Expression (CTE):

1WITH RankedStaff AS (
  SELECT 
      emp_id,
      first_name,
      last_name,
      department,
      salary,
      DENSE_RANK() OVER (
          PARTITION BY department 
          ORDER BY salary DESC
      ) AS salary_rank
  FROM employees
13)
14SELECT 
  department,
  salary_rank,
  first_name,
  last_name,
  salary
20FROM RankedStaff
21WHERE salary_rank <= 2
22ORDER BY department, salary_rank;

Additional Ranking Functions: PERCENT_RANK & NTILE

MySQL 8.0 also includes:

NTILE(n): Distributes rows evenly into n roughly equal buckets (e.g., NTILE(4) divides customer spend into quartiles).
PERCENT_RANK(): Calculates relative rank as a percentage between 0.0 and 1.0 using the formula (rank - 1) / (total_rows - 1).

1-- Divide customers into spending quartiles (Top 25%, Upper-Mid, Lower-Mid, Bottom 25%)
2SELECT 
3    customer_id,
4    lifetime_spend,
5    NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS spend_quartile
6FROM customer_aggregates;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

If two rows tie for second place, what sequence will RANK() produce for the first four rows?

Question 2

Which ranking function guarantees no gaps in the numbering sequence even when ties occur?

Question 3

What does ROW_NUMBER() do when two rows have identical sorting values?

Question 4

How do you retrieve the top 3 highest revenue products per category?

Question 5

What does NTILE(4) accomplish on an ordered dataset?

Next Lesson

Positional Value Functions: LEAD and LAG

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
The OVER() Clause & PARTITION BY Mechanics	Positional Value Functions: LEAD and LAG

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next