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.

ID	NAME	MATHEMATICS	PHYSICS	CHEMISTRY
501	Surya	99	97	85
502	Sravan	91	98	94
503	Charan	99	93	88
504	Ram	92	99	92
505	Aryan	94	99	88
506	Sathwik	91	88	91
507	Madhav	90	97	89

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.

ID	NAME	MATHEMATICS	RANK
501	Surya	99	1
502	Charan	99	1
505	Aryan	94	3
504	Ram	92	4
506	Sathwik	91	5
503	Sravan	91	5
507	Madhav	90	7

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.

My Personal Notes arrow_drop_up

Save