MySQL RANK

The MySQL RANK function is used to assign a rank to each row within the result set of a query based on the values of one or more columns. It is commonly used in scenarios where you want to determine the rank of each row relative to others, such as in competitions, rankings, or leaderboards.

Syntax

Here is the basic syntax of the RANK function in MySQL:

RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

PARTITION BY: Optional clause that divides the result set into partitions to which the RANK function is applied independently. If not specified, the function treats the whole result set as a single partition.

ORDER BY: Specifies the columns based on which the ranking is determined. You can specify one or more columns, and you can also specify the sort order (ascending or descending) for each column.

Example

Now, let’s look at an example to illustrate the usage of the RANK function. Consider a table named scores with columns student_name and score:

CREATE TABLE scores (
    student_name VARCHAR(255),
    score INT
);

INSERT INTO scores (student_name, score) VALUES
    ('Alice', 85),
    ('Bob', 92),
    ('Charlie', 78),
    ('David', 92),
    ('Eva', 88);

To rank the students based on their scores, you can use the RANK function:

SELECT
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS score_rank
FROM
    scores;

In this example, the RANK function is applied to the score column in descending order. The result will show each student’s name, their score, and the corresponding rank based on the score. If two students have the same score, they will be assigned the same rank, and the next rank will be skipped.

The output of the query might look like this:

+--------------+-------+------------+
| student_name | score | score_rank |
+--------------+-------+------------+
| Bob          |    92 |          1 |
| David        |    92 |          1 |
| Eva          |    88 |          3 |
| Alice        |    85 |          4 |
| Charlie      |    78 |          5 |
+--------------+-------+------------+

In this result set, Bob and David share the first rank since they both have the highest score, and Eva is ranked third, and so on.

The RANK function is a versatile function for ranking data within partitions in MySQL. It can be used to perform various data analyses and identify meaningful insights within your datasets.