If your interested to learn about the MySQL Number format function
MySQL uses a ranking function that allows us to rank each row of a partition in the databases. The ranking functions are also a sub-part of a window function in MySQL. The ranking functions in MySQL can be used with the following clauses:
- They always work with the OVER()
- They assign a rank to each row based on the ORDER BY
- They assign a rank to each row in the sequential order.
- They always assign a rank to rows, starting with one for each new partition.
Types of Ranking
There are 3 types of ranking functions supported in MySQL-

- dense_rank():
This function will assign rank to each row within a partition without gaps. Basically, the ranks are assigned in a consecutive manner i.e if there is a tie between values then they will be assigned the same rank, and next rank value will be one greater then the previous rank assigned. - rank():
This function will assign rank to each row within a partition with gaps. Here, ranks are assigned in a non-consecutive manner i.e if there is a tie between values then they will be assigned same rank, and next rank value will be previous rank + no of peers(duplicates). - percent_rank():
It returns the percentile rank of a row within a partition that ranges from 0 to 1. It tells the percentage of partition values less than the value in the current row, excluding the highest value.
NOTE: It is to be noted that MySQL provides support for the ranking and window functions since version 8.0.
MySQL supports the following three types of ranking functions:
- Dense Rank
- Rank
- Percent Rank
Now, we are going to discuss each ranking functions in detail:
MySQL dense_rank()
It is a function that assigns a rank for every row within a partition or result set without any gaps. The rank of rows is always assigned in consecutive order (increased by one from the previous row). Sometimes you will get a tie between the values, then the dense_rank will assign it with the same rank, and its next rank will be its next consecutive number.43.3M703Hello Java Program for Beginners. The following are the syntax of dense_rank():
SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
In the above syntax, the PARTITION BY clause partition the result set return by FROM clause, and then the dense_rank function applied on each partition. Next, the ORDER BY clause applies to each partition to specify the order of rows.
Example 1
Let us understand how MySQL dense_rank() function works. So, first, create a table that contains the following data:
Table: employees

This statement uses the dense_rank() function for assigning the rank value for each row.
SELECT emp_id, emp_name, city, emp_age, DENSE_RANK() OVER (ORDER BY emp_age) dens_rank FROM employees;
After executing the above statement, we will get the following output:

Example 2
Let us see another example that divides the result set into partitions. The following statement uses dense_rank() function to assign the value on each row and divide the result set into partition using emp_age:
SELECT emp_id, emp_name, city, emp_age, DENSE_RANK() OVER (PARTITION BY emp_age ORDER BY city) dens_rank FROM employees;
After the successful execution of the above query, we will get the following output:

MySQL rank()
It is a function that assigns a rank for every row within a partition or result set with gaps. The rank of rows is always not-assigned in a consecutive order (i.e., increased by one from the previous row). Sometimes you will get a tie between the values, then the rank() function will assign it with the same rank, and the next rank value will be its previous rank plus a number of duplicate numbers.
The following are the syntax of rank():
SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
In the above syntax, the PARTITION BY clause partition the result set return by , and then the rank() function applies on each partition and re-initialized when the partition boundary crosses other partition. Next, the ORDER BY clause applies on each partition to sorts the rows based on one or more columns names.
Let us take a table that we have created previously and see the working of rank() with different examples.
Table: employees

Example 1
This statement uses the rank() function for assigning the rank value for each row.
SELECT emp_id, emp_name, city, emp_age, RANK() OVER (ORDER BY emp_age) my_rank FROM employees;
The above query will give the following output:

Example 2
Let us see another example that divides the result set into partitions. The following statement uses the rank() function to assign the value on each row and divide the result set into partition using emp_age and sorts them based on emp_id:
SELECT *, RANK() OVER (PARTITION BY emp_age ORDER BY emp_id) my_rank FROM employees;
Executing the above statement, we will get the following output:

MySQL percent_rank()
It is a function that calculates a percentile rank (relative rank) for rows within a partition or result set. This function returns a number from a range of values between 0 and 1. The following are the syntax of percent_rank():
SELECT column_name PERCENT_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
For a specified row, this function calculates the rank by using the following formula:
(rank-1) / ( total_rows-1)
Here,
rank: It is the rank of each row returns by rank() function.
total_rows: It represents the total number of rows present in the partition.
NOTE: It is to make sure that when you use this function, you must have to use the ORDER BY clause. Otherwise, all rows are considered duplicates and assigned the same rank, which is 1.
Let us create a table “students” that contains the following data and see the working of percent_rank() function in MySQL.
Table: students

Example 1
This statement uses the percent_rank() function for calculating the rank value for each row order by marks column.
SELECT stud_id, stud_name, subject, marks, PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank FROM students;
The above query will give the following output:

To see how the above formula works, consider the following query:
SELECT stud_id, stud_name, subject, marks, rank() OVER ( partition by subject order by marks )-1 AS 'rank-1', count(*) over (partition by subject)-1 AS 'total_rows-1', PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank FROM students;
It will give the following output:

More Examples
MySQL RANK()
function example
Let’s use the sales
table created in the window function tutorial for the demonstration. If you have not created the sales
table yet, here is the script:
<code>CREATE TABLE IF NOT EXISTS sales( sales_employee VARCHAR(50) NOT NULL, fiscal_year INT NOT NULL, sale DECIMAL(14,2) NOT NULL, PRIMARY KEY(sales_employee,fiscal_year) ); INSERT INTO sales(sales_employee,fiscal_year,sale) VALUES('Bob',2016,100), ('Bob',2017,150), ('Bob',2018,200), ('Alice',2016,150), ('Alice',2017,100), ('Alice',2018,200), ('John',2016,200), ('John',2017,150), ('John',2018,250); SELECT * FROM sales;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
The following picture shows the data of the sales
table:

The following statement use the RANK()
function to rank the sales employees by sales amount in every year:
<code>SELECT sales_employee, fiscal_year, sale, RANK() OVER (PARTITION BY fiscal_year ORDER BY sale DESC ) sales_rank FROM sales; </code><small>Code language: SQL (Structured Query Language) (sql)</small>
In this example:
- First, the
PARTITION BY
clause breaks the result sets into partitions by fiscal year. - Then, the
ORDER BY
clause sorts the sales employees by sales in descending order.
MySQL RANK()
function with CTE example
The following statement uses the RANK()
function to find the top three highest valued-orders in each year:
<code>WITH order_values AS( SELECT orderNumber, YEAR(orderDate) order_year, quantityOrdered*priceEach AS order_value, RANK() OVER ( PARTITION BY YEAR(orderDate) ORDER BY quantityOrdered*priceEach DESC ) order_value_rank FROM orders INNER JOIN orderDetails USING (orderNumber) ) SELECT * FROM order_values WHERE order_value_rank <=3; </code><small>Code language: SQL (Structured Query Language) (sql)</small>
Here is the output:

In this example:
- First, we used a common table expression (CTE) to get the order number, order year, and the rank. To rank orders by order value in each year, we used the
RANK()
function that partitioned the rows by order year and sorted the order value in descending order. - Then, we selected only the orders whose rank is less than or equal three.
In this tutorial, you have learned how to use the MySQL RANK()
function to assign a rank to each row in a result set.