SQL Query to Add Ranking Positions of Rows in a Database With RANK()

In this article, we will discuss the overview of SQL function RANK and then our main focus will be on Add Ranking Positions of Rows in a Database With RANK() in SQL. Let’s discuss it one by one.

Overview :
Generally, when max/min commands are executed, a single row is displayed as output. RANK() is an SQL function introduced in order to rank the rows according to some attribute in the table. Based on the record of the existing value, the RANK function will help the column to be ranked accordingly.

Syntax :

RANK() OVER(ORDER BY Any Column)

Steps to implement RANK function :
Here, we will discuss the steps to implement the RANK function in SQL. 

Step-1: Reference table :
Consider a table is created based on marks of students in the class that contains data displayed below.

Step-2: Creating a table :
Now, the SQL statement used to create the table is given as follows.

CREATE TABLE MarkList
(
id int,
name varchar(20),
mathematics int, 
physics int,
chemistry int
);

Step-3: Inserting data :
Here, we will insert the rows into the table as follows.

insert into MarkList values( 501,'Surya',99,97,85);
insert into MarkList values(502,'Charan',99,93,88);
insert into MarkList values(503,'Sravan',91,98,94);
insert into MarkList values(504,'Ram',92,99,82);
insert into MarkList values(505,'Aryan',94,99,88);
insert into MarkList values(506,'Sathwik',91,88,91);
insert into MarkList values(507,'Madhav',90,97,89);

Step-4: Verifying and ranking data :
Now, if we want ranks based on the scores of mathematics, then the query is written as follows.

SELECT id, name, mathematics,
RANK() OVER(ORDER BY Mathematics DESC) 
as 
'Rank' from MarkList;

Output :
The output will be as follows.

Explanation :

• When the SQL command is executed, the function checks for the order and assigns the ranks to the respective rows.
• Then the table is displayed with the ranks as per the given code.

Other Methods :     
There are many other methods that can be used in the place of RANK(). Some of them are listed here.

• ROW_NUMBER() just adds the row number in the place of the rank.
• DENSE_RANK() just gives the next number as the next rank. This does not depend on the frequency of other ranks.