MySQL RANK Function

MySQL RANK Function

Understanding the MySQL RANK() Function

The RANK() function in MySQL is a window function that assigns a unique rank to each row within a result set based on the ordering of a specified column. When rows have the same value in the ordered column, they receive the same rank. However, the next rank is skipped, meaning that if two rows are ranked #1, the next row will be ranked #3, not #2.


This can be useful in scenarios where you need to rank records, such as ranking employees by salary, athletes by performance, or products by sales.

Syntax of RANK()

RANK() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] )
  • PARTITION BY: This optional clause divides the result set into partitions (groups) to which the RANK() function is applied. Without it, the entire result set is treated as one group.
  • ORDER BY: Specifies the column(s) by which the data will be ordered. The ranking will be based on this ordering.
  • ASC|DESC: Specifies whether the order is ascending or descending. The default is ascending.

How It Works

The function works by assigning ranks based on the specified ORDER BY clause. Rows with the same value (in the ORDER BY column) will share the same rank. After that, the next distinct value gets a rank based on the number of tied rows before it.

For example, consider this table of employees:

EmployeeSalary
Alice1000
Bob1200
Charlie1200
Dave1100

If we rank employees by their salary in descending order, the query would look like this:

SELECT Employee, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM employees;

The result would be:

EmployeeSalaryRank
Bob12001
Charlie12001
Dave11003
Alice10004

Notice that Bob and Charlie have the same salary, so they both receive a rank of 1. The next rank is 3, not 2, because two employees were ranked 1.

Key Points to Remember

  • The RANK() function does not provide consecutive ranking when there are ties.
  • If there are n rows tied for a rank, the next rank is n+1. For example, if two people are ranked #1, the next person is ranked #3.
  • You can use the PARTITION BY clause to divide your data into subsets and rank within each subset.

Example with PARTITION BY

Consider a scenario where we want to rank employees by salary within each department. Let’s assume we have an additional column for department:

EmployeeDepartmentSalary
AliceHR1000
BobIT1200
CharlieIT1200
DaveHR1100

To rank employees by salary within each department, the query would be:

SELECT Employee, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM employees;

This would produce:

EmployeeDepartmentSalaryRank
AliceHR10002
DaveHR11001
BobIT12001
CharlieIT12001

Here, employees are ranked within each department. In the IT department, Bob and Charlie have the same salary and thus share rank 1. In the HR department, Dave has the highest salary, so he’s ranked #1, and Alice is ranked #2.

Use Cases for RANK()

  1. Leaderboards: Ranking users or players based on scores, where tied scores get the same rank.
  2. Employee Rankings: Ranking employees based on performance metrics such as sales numbers or customer satisfaction ratings.
  3. Sales and Marketing: Ranking products based on sales performance, or ranking stores based on revenue generation.
  4. Competitions: Ranking participants based on times, scores, or points, where some participants may tie.

Conclusion

The RANK() function is powerful when you need to assign ranks to data based on some criteria and handle ties in a clear and structured manner. Whether you're ranking employees, products, or athletes, RANK() provides a simple yet effective solution to create rankings in MySQL.

You can adjust the examples to suit your audience, whether they’re beginners or more experienced MySQL users!

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close