Post
Ask Question

Uses of Row_number, Rank and Dense Rank in SQL Server

Introduction
 
In this article, we will discuss how to use the ranking functions in SQL Server. Please create the sample student mark table and insert the records as given below:
  1. Create Table StudentMark  
  2. (  
  3. StudentId Bigint Identity(1,1),  
  4. StudentName Varchar(50),  
  5. SubMarks Numeric(18,1)  
  6. )  
  7. Insert Into StudentMark values ('raj',99)  
  8. Insert Into StudentMark values ('ram',90)  
  9. Insert Into StudentMark values ('baskar',85)  
  10. Insert Into StudentMark values ('naveen',90)  
  11. Insert Into StudentMark values ('kumar',98)  
  12. Insert Into StudentMark values ('arul',99)  
  13. Insert Into StudentMark values ('tharun',95)  
  14. Insert Into StudentMark values ('karthi',91)  
  15. Insert Into StudentMark values ('anand',90)  
  16. Select * from StudentMark  
ROW_NUMBER()
 
The ROW_NUMBER() ranking function returns a unique sequential number for each row within the partition of the specified, starting at 1 for the first row in each partition and without repeating or skipping numbers in the ranking result of each partition. If there are duplicate values within the row set, the ranking ID numbers will be assigned arbitrarily.
  1. Select *,ROW_NUMBER() Over(Order by SubMarks desc) as RowNumber From StudentMark    
 
 
If the PARTITION BY clause is specified, the ranking row number will be reset for each partition. In the previously created table, the query below shows how to use the ROW_NUMBER ranking function to rank the StudentMark table rows according to the marks of each student.
  1. Select *,ROW_NUMBER() Over(Partition by SubMarks Order by SubMarks) as RowNumber From StudentMark  
 
 
RANK()
 
The RANK() ranking function returns a unique rank number for each distinct row to a specified column value, starting at 1 for the first row in each partition, with the same rank for duplicate values and leaving gaps between the ranks, this gap appears in the sequence after the duplicate values.
  1. Select *,RANK() Over(Order by SubMarks Desc) as 'Rank' From StudentMark  
 
 
DENSE_RANK()
 
The DENSE_RANK() ranking function is similar to the RANK() function by generating a unique rank number for each distinct row to a specified column value, starting at 1 for the first row in each partition, ranking the rows with equal values with the same rank number. However, it doesn't skip any rank, therefore leaving no gaps between ranks.
  1. Select *,DENSE_RANK() Over(Order by SubMarks Desc) as 'Dense Rank' From StudentMark  
 
 
Summary
 
In this article, we learned about Row_Number() ,Rank(), and Dense_Rank() in SQL Server.