SQL SERVER – Ranking Functions

Ranking functions return a ranking value for each row. Depending on the function that is used, some rows might receive the same value as other rows while some receive one rank plus the number of distinct ranks that come before the current row.

Let us first create the Student Marks table.

Create Table StudentMarks(StudentId int identity(1,1),Name varchar(25),Paper1 int,Paper2 int,Paper3 int,Batch_no int)

Insert Into StudentMarks
Select 'AAA',90,95,99,1
Union All
Select 'BBB',90,94,98,1
Union All
Select 'CCC',90,93,99,1
Union All
Select 'DDD',91,92,97,2
Union All
Select 'EEE',97,99,99,2
Union All
Select 'FFF',97,92,98,2
Union All
Select 'GGG',91,92,93,2
Union All
Select 'HHH',95,95,95,2
Union All
Select 'III',91,93,95,3
Union All
Select 'JJJ',90,95,99,3
Union All
Select 'KKK',91,93,99,3
Union All
Select 'LLL',96,91,99,3

Now let us first retrieve the records from the table.

Select * From StudentMarks

Now let us see the Ranking Functions one by one, using the above table. 

1. RANK() : Returns the rank of each row.If two or more rows tie for a rank, each tied rows receives the same rank.

Example:                                                                  Select Studentid,Name,Paper1,
Rank() over(Order by paper1 desc) 'Rank'
From StudentMarks

Output :

Here the first two Rows i.e. Paper1 marks for the first two students are same. Hence they both will receive the same Rank i.e.  Rank 1. Since the first two ranks were given, the third student will receive the Rank 3 instead of 2. The ranking will follows  in the same manner for the remaining rows.

2. DENSE_RANK() : Returns the Rank of each row, without any gaps in the ranking. If two or more rows tie for a rank, each tied rows receives the same Rank. The next rank is one plus the number of distinct ranks that come before the current row.

Example:                                                              Select Studentid,Name,Paper1,
Dense_Rank() over(Order by paper1 desc) 'Dense_Rank'
From StudentMarks

Output :

Here the first two Rows i.e. Paper1 marks for the first two students are same. Hence they both will receive the same rank i.e  Rank 1. Here Unlike RANK(), the next student will  receive Rank 2 instead of 3. That means the next rank will be, 1  +  1 (the Number of distinct ranks that come before the current row) i.e. 2.

3. NTILE(n) : Divides all Rows in an ordered partitions into ‘n’ number of groups. The groups are numbered,starting at 1.

Example:                                                                                  Select Studentid,Name,Paper1,
NTILE(4) over(Order by Paper1 desc) 'Group'
From StudentMarks

Output :

As shown in the above example, all the 12 rows were divided into 4 groups in an order partition.

4. ROW_Number()  : Displays Sequential Row Number for each of the row with in the partition starting at 1. If we are not using the ‘Partition By’ clause, then it will display the sequential row number continuously for each of the row. 

Example 1 (Using Order By clause):                               Select 
ROW_NUMBER() over(Order by Paper1 desc) 'Record_No',
Studentid,Name,Paper1
From StudentMarks

Output :

As shown in the above example, as we are not partitioned the rows, the row number is displaying sequentially from 1 to 12.

Example 2 (Using Partition By clause):                         Select 
ROW_NUMBER() over(Partition By Batch_no Order by Paper1 desc) 'Record_No',
Studentid,Name,Paper1,Batch_no
From StudentMarks

Output :

As shown in the above example, For each partition, Here  Batch_no, a new record or row number will be displayed. As three distinct batches are available in the table, On applying the Partition on Batch_no, for each partition a new set of row numbers will  be displayed,  each starting at 1.