Skip to content
Related Articles

Related Articles

SQL Query to Add Ranking Positions of Rows in a Database With RANK()
  • Difficulty Level : Expert
  • Last Updated : 13 Apr, 2021

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.



IDNAME

MATHEMATICS

PHYSICS CHEMISTRY 
501Surya

99

97

85

502Sravan

91

98

94

503Charan

99

93

88

504Ram

92



99

92

505Aryan

94

99

88

506Sathwik

91

88

91

507Madhav

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.

IDNAME

MATHEMATICS

RANK
501Surya

99

1

502Charan

99

1

505Aryan

94

3

504Ram

92

4

506Sathwik

91

5

503Sravan

91

5

507Madhav

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
Recommended Articles
Page :