How To Rank Over Partition in MySQL

Sometimes you may need to rank over partition in MySQL or do grouped ranking in MySQL. We will look at how to rank over partition using MySQL rank function. You can use this approach to rank within each group/partition in a table separately. However, please note, this function is available from MySQL 8.0 only.

MySQL Rank over Partition

We will use the MySQL RANK() function to rank within group. Here’s the syntax of MySQL RANK function.

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

The rank function assigns a rank to each row within a partition such that each row’s rank is one more than total number of rows that have been ranked in that partition.

In the above syntax, PARTITION BY clause will divide the rows into partitions. You can partition a table based on one or more column values. If you don’t want to partition the table, you can simply omit the PARTITION BY clause altogether.

Bonus Read : How to Change MySQL time zone in query

The Rank function will calculate rank within each partition/group and re-initialize the rank number when starting with a new partition.

ORDER BY clause sorts the rows within each partition based on values in one or more columns.

Bonus Read : MySQL Compare databases

Let us say you have the following table.

mysql> create table for_rank(id int, month  varchar(10),amount  int);

mysql> insert into for_rank(id, month, amount)
     values(1,'Jan',450),
     (2,'Jan',350),
     (3,'Jan',250),
     (4,'Feb',150),
     (5,'Feb',450),
     (6,'Feb',500),
     (7,'Mar',350),
     (8,'Mar',450),
     (9,'Mar',250),
     (10,'Mar',150);

mysql> select * from for_rank;
+------+-------+--------+
| id   | month | amount |
+------+-------+--------+
|    1 | Jan   |    450 |
|    2 | Jan   |    350 |
|    3 | Jan   |    250 |
|    4 | Feb   |    150 |
|    5 | Feb   |    450 |
|    6 | Feb   |    500 |
|    7 | Mar   |    350 |
|    8 | Mar   |    450 |
|    9 | Mar   |    250 |
|   10 | Mar   |    150 |
+------+-------+--------+

Bonus Read : MySQL Show Indexes in databases

Here’s the query to assign rank to each row of the table, without using any PARTITION BY clause.

SELECT
     id, month, amount, RANK() OVER (
     PARTITION BY Month
     ORDER BY amount desc
     ) my_rank
FROM for_rank;

+------+-------+--------+--------+
| id   | month | amount |  rank  |
+------+-------+--------+--------+
|    6 | Feb   |    500 |     1  |
|    5 | Feb   |    450 |     2  |
|    4 | Feb   |    150 |     3  |
|    1 | Jan   |    450 |     1  |
|    2 | Jan   |    350 |     2  |
|    3 | Jan   |    250 |     3  |
|    8 | Mar   |    450 |     1  |
|    7 | Mar   |    350 |     2  |
|    9 | Mar   |    250 |     3  |
|   10 | Mar   |    150 |     4  |
+------+-------+--------+--------+

In the above query, we partition the table by month name, and then rank each row within each partition in descending order of amount.

Hopefully now you can easily use MySQL Rank function to rank over partition and groups.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!