SQL RANK Function

The RANK is an analytic function or window function in the Standard Query Language (SQL), which indicates the position of each row in the result set based on the values of the columns specified when the query is run.

The RANK function is used to rank data present in the table, for example, ranking students' positions based on percentage, ranking employees' positions based on salaries, etc.

When ranking is done, it gives a number to each row displayed as output. Ranks are provided in successive order, i.e., 1, 2, 3, 4, etc. The RANK () function provides the same rank to more than one row if the rows are the same; for example, if two students get the same percentage, they will both get the same rank.

Things that must be kept in mind while using the rank function are as follows:

• Using the OVER() clause is compulsory because the RANK() function works with the OVER() clause.
• It ranks each row based on the ORDER BY clause.
• It ranks the rows, starting with the integer 1 for each new partition.
• It ranks to each row in successive order, i.e., 1, 2, 3, 4, etc.
• If more than one row is the same, it gives those rows the same rank. But in this scenario, the next unequal row is given the rank by adding the number of duplicates in the previous rank.

Shown below is the syntax for using the RANK() function in SQL:

In the above syntax,

• The RANK() function is used with the OVER() clause.
• The PARTITION BY clause splits the output obtained by the FROM clause into divisions. It is not necessary to use this PARTITION BY clause.
• ORDER BY clause is used to obtain the order of the rows based on the column name in descending or ascending ranking.

There are various rank functions in Standard Query Language (SQL), which are as follows:

• RANK()
• ROW_NUMBER()
• DENSE_RANK()
• NTILE()

We are going to discuss each of them with examples.

Examples of RANK function:

To begin with, we will make a sample table on which we will work.

We will make a tabled named student_table, containing the details of stu_name, stu_age, stu_address, stu_subject, and stu_marks.

Creating table:

The code given below will create an empty table.

An empty table will look like it is shown below.

Inserting data into the table:

The code given below will insert data into the table.

As you see, data has been inserted, and the table looks like this:

Example 1: RANK() function in SQL without using PARTITION BY clause

In this example, we will rank the students based on their marks. To do this, we will use the code below.

Input:

Output:

As you can see in the output, students are provided a rank on the basis of their marks. Students who have the same marks are given the same rank, but after that, the rank is given to the next student by adding the number of duplicates in the previous rank.

Example 2: RANK() function in SQL using PARTITION BY clause

In this example, we will divide the subjects into partitions using PARTITION BY clause, and then we will give students rank in descending order based on marks using the ORDER BY clause.

We will use the code below to give rank to students,

Input:

Output:

As you can see in the output, students have been ranked subject-wise based on their marks.

Example 3: ROW_NUMBER() RANK function in SQL

In this example, we will comprehend how to use ROW_NUMBER() RANK function.

ROW_NUMBER() gives a unique rank to each row, which means even if two students have the same marks, they will be given a unique rank. The syntax of the ROW_NUMBER() function is almost identical to that of the RANK() function.

The code given below will show the use of the ROW_NUMBER() function-

Input:

Output:

As you can see in the output, each row is assigned a different rank.

Example 4: DENSE_RANK() function in SQL

In this example, we will comprehend how to use DENSE_RANK() function.

DENSE_RANK() is used to assign a unique rank to each row. If two students have the same marks, it assigns the same rank to both. The syntax of the DENSE_RANK() function is almost identical to the syntax of the RANK() function. The below code will show the usage of the DENSE_RANK() function-

Input:

Output:

The output clearly shows that the next rank is not skipped when students are given the same rank for the same marks. Instead, it provides the next sequential integer.

Example 5: NTILE(N) RANK function in SQL

In this example, we will learn to use NLITE(N) RANK function.

NTILE(N) divides rows into groups and assigns a group number to each row. 'N' denotes the number of groups. The syntax for NLITE(N) function is almost the same as that of the RANK() function.

The code given below will show the use of the NTILE(N) function-

Input:

Output:

It is evident from the output that the rows are divided into three groups.

Conclusion:

In this article, you have gained knowledge about SQL RANK Function, along with various examples. It is a function that assigns a rank to each row in a table. You have also learned about different RANK functions, which are DENSE_RANK(), ROW_NUMBER(), RANK(), and NTILE(N) rank functions.