MySQL RANK
Overview
MySQL rank() function assigns the ranking number to the rows of the result set or the partition and ranking numbers are skipped by the MySQL rank() function if there are peers. Apart from that, we can also use the MySQL rank() function with partitions for assigning the ranks to the partition rows. The rank starts from 1 for the new partition. MySQL version 8.0 and onwards version support the rank() function.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreExceptions 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.
Turn Learning into Career Growth
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:
Conclusion
- MySQL rank() function assigns the ranking number to the rows of the result set or the partition.
- For partitioning the result set into different groups we use Partition by clause and which is an optional clause in the MySQL rank() function.
- Order by clause is also an optional clause used for specifying the order of assigning the rank number.
- 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 number of duplicate rows.