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.
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.
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 : MATHEMATICS 99 85 91 94 99 88 92 92 94 88 91 91 90 89
Consider a table is created based on marks of students in the class that contains data displayed below.
ID NAME PHYSICS CHEMISTRY 501 Surya 97 502 Sravan 98 503 Charan 93 504 Ram> 99 505 Aryan 99 506 Sathwik 88 507 Madhav 97
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 mathemat
SELECT id, name, mathematics, RANK() OVER(ORDER BY Mathematics DESC) as 'Rank' from MarkList;
Output : MATHEMATICS 99 1 99 1 94 3 92 4 91 5 91 5 90 7
The output will be as follows.
ID NAME RANK 501 Surya 502 Charan 505 Aryan 504 Ram 506 Sathwik 503 Sravan 507 Madhav
- 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.