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:
- Rank what?
- Within what group?
- Rank by what?
Now, let’s check out the basic syntax of the RANK() function in SQL.
Syntax
SELECT column_name,
RANK() OVER (PARTITION BY... ORDER BY...) as rank
FROM table_name;
In this syntax:
- The column_name represents the column that you wish to rank in the table
- The PARTITION BY clause divides the result set's rows into partitions based on one or more parameters
- The ORDER BY clause sorts the rows in each partition where the function is applied
FREE Course: Introduction to Data Analytics
Mastery to Data Analytics Basics is a Click Away!Start Learning
Example
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.
Output
As you can see that the students have been ranked according to their marks in the above table.
Using SQL RANK() Function Over the Result Set
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.
Output
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.
Using SQL RANK() Function Over Partition
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.
Output
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.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!
Conclusion
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.
