In SQL, it is common to perform different aggregated functions like MIN, MAX, and AVG. After performing these functions, you get the output as a single row. To define the ranks for each field individually, the SQL server provides a RANK() function. The RANK() function allocates a rank, that is, an integer number to each row within a group of data sets. The RANK() function is also known as the window function. Before using the  MYSQL RANK() function, it is important to identify three questions:
Now, let’s check out the basic syntax of the RANK() function in SQL.
SELECT column_name,
RANK() OVER (PARTITION BY... ORDER BY...) as rank
FROM table_name;
In this syntax:

Let’s consider the table given below to rank the given STUDENTNAME based on STUDENT MARKS.

The code below will rank the StudentName, based on StudentMarks as the rank would be stored in a new column StudentRank.


As you can see that the students have been ranked according to their marks in the above table.
In this example, you will find out how to use the RANK() function over a result set. The given query is used to rank all the students based on their marks.

Now, let’s see the output for the above query. 

As you can see in the above example, that the PARTITION BY clause is missing, so the query treats the whole result set as a single partition. 

The ORDER BY clause is used to sort the rows based on student marks. The RANK() function then applied results in the rows in descending order by student marks.
Now, for understanding the RANK() function over the partition, add 3 more rows to the table that you created earlier, to understand the PARTITION BY clause more clearly.

Now, this is the table on which you’ll apply the RANK() function. In the table above, you added 3 more students, Peter, Bob and Kim.


Note: the table above is partitioned by the Class name, and each student in each class is ranked differently. This means that the ORDER BY clause is applied to each partition separately as was mentioned earlier.
The students in each class are partitioned separately and ranked accordingly. There is only one student in each Class 10, Class 4, and Class 7, therefore, the students in these classes have their rank as 1. 
There are three different students in Class 3, and therefore they are ranked in decreasing order by Student marks as mentioned in the ORDER BY clause. Similarly, the students in Class 9 have been ranked. Since they both have the same marks, they both gain rank 1.
With this, we come to an end to the Rank() function in SQL. Now that you have learned about the Rank() function, it’s time for you to learn and explore other functions and clauses that the SQL server provides and move on to become an expert in this field. If you wish to get certified and master the A to Z of SQL, you must check out Simplilearn’s SQL certification training. 
If you have any doubts regarding this tutorial, feel free to drop them in the comments section, and our experts will answer them for you.
Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.
Cohort Starts: 15 Apr, 2024
Cohort Starts: 16 Feb, 2024
Cohort Starts: 28 Feb, 2024
Cohort Starts: 19 Mar, 2024
Azure Functions: A Comprehensive Guide for Beginners
What is the SetPrecision Function in C++ and Examples
How to Rank #1 on Google
Introduction To AWS Lambda: Building Functions and Apps
SQL: String Functions
The Easiest Way to Understand C++ Functions
© 2009 -2024- Simplilearn Solutions
Follow us!
Company
Work with us
Discover
For Businesses
Learn On the Go!
Trending Post Graduate Programs
Trending Bootcamp Programs
Trending Master Programs
Trending Courses
Trending Categories
Trending Resources

source