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
FREE Course: Introduction to Data Analytics

Example

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

RankInSQL_1

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

RankInSQL_2.

Output

RankInSQL_3

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.

RankInSQL_4.

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

Output

RankInSQL_5

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. 

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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.

RankInSQL_6

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.

RankInSQL_7.

Output

RankInSQL_8

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.