MySQL RANK

Syntax of MySQL RANK() Function

Parameters of MySQL RANK() Function

• Partition by Clause:
  For partitioning the result set into different groups we use Partition by clause and which is an optional clause in the MySQL rank() function. If partition by clause is not provided then the whole result is treated as one partition.
• Order by Clause:
  Order by clause is used for specifying the order of assigning the rank number. And this is also an optional clause. asc|desc can be written with the order by clause for ascending and descending order of the ranking, and if have not specified asc|desc then by default it will use ascending order.

Return Value of MySQL RANK() Function

The rank assigned to every row is returned by the MySQL rank() function.

Exceptions of MySQL RANK() Function

• If the rank() function is used with the query that has LIMIT clause, then the rank assignment for the rows excluded from the limit may be inaccurate.
• Rank is assigned according to the defined groups if the rank() function is used with the query that has GROUP BY clause.

How Does the MySQL RANK() Function Work?

MySQL RANK() function assigns the ranking number to the rows of the partition or results set based on the ordering specified by the order by clause. It will not assign consecutive rankings to the rows. If duplicate rows exist then it assigns the same ranking to the duplicate rows and the row after the duplicate row will get the ranking as the sum of the previous rank and the number of duplicate rows. For instance, if the first three rows have the same value, then 1 is assigned by the rank function to all three rows, and 3 is assigned to the fourth row.

Examples

Creating a Table:

Let us create a table with the name STUDENT and we will store the STUDENT_NAME, STUDENT_CLASS, and STUDENT_PERCENTAGE in the table. So the code for creating the STUDENT table is given below:

Query:

Inserting Data Into the Table:

Now let us insert some data into the table.

Query:

Fetching Data of The Table:

Now first we all fetch all data of the table by writing the query given below:

Query:

Output:

Example1: Using Mysql Rank() Function:

Now we will rank() function to get the data of the students with the rank assigned to them based on their percentage.

Query:

Output:

In the above query, we have not specified the asc|desc with the order by clause so it has automatically arranged the student_percentage into ascending order.

Example2: Using Desc with The Order by Clause in My Sql Rank() Function

If we want ranking will be assigned in the decreasing order of the STUDENT_PERCENTAGE, then we need to write the query given below:

Query:

Output:

Example3: Using Partition by Clause with My Sql Rank() Function

We can also assign a rank to the students class-wise. Then we have to write partition by clause with the STUDENT_CLASS column so that the class-wise rank is assigned to the students based on their percentage.

Query:

Output:

See Also

• ORDER BY Clause in SQL
• SQL GROUP BY Clause
• SQL HAVING Clause