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

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program
Your Data Analytics Career is Around The Corner!

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. 

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.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Post Graduate Program in Data Analytics

Cohort Starts: 22 Nov, 2024

8 months$ 3,500
Post Graduate Program in Data Science

Cohort Starts: 9 Dec, 2024

11 months$ 3,800
Professional Certificate in Data Analytics and Generative AI

Cohort Starts: 10 Dec, 2024

22 weeks$ 4,000
Professional Certificate Program in Data Engineering

Cohort Starts: 16 Dec, 2024

7 months$ 3,850
Caltech Post Graduate Program in Data Science

Cohort Starts: 23 Dec, 2024

11 months$ 4,000
Data Scientist11 months$ 1,449
Data Analyst11 months$ 1,449

Recommended Reads