Article

Implement Ranking Functions in SQL Server

13 Mar 2018 Alok Kushwaha
0 Comments 848 Views



There are four ranking function in SQL Server. ROW_NUMBER, RANK, DENSE_RANK, and NTILE are use to return a ranking value for each row over the partition.

 

  ROW_NUMBER

  This function return the sequantial number of the rows based on the order clause.

  RANK

  This function return the rank of a row over a partition of a result set.
  
  DENSE_RANK
  
  This function return the rank of a row over a partition of a result set with out any gaps in the ranking.
  
  NTILE

  This function assign the rank number to each record present in a partition into a specified number of groups.

Examples

select * from Employee  

select * ,
row_number() over (order by Salary desc) as [Row_number],
rank() over (order by Salary desc) as [Rank],
dense_rank() over (order by Salary desc) as [Dense_rank],
ntile(3) over (order by Salary desc) as [Ntile]
from Employee 

select * ,
row_number() over (partition by Department order by Salary desc) as [Row_number],
rank() over (partition by Department order by Salary desc) as [Rank],
dense_rank() over (partition by Department order by Salary desc) as [Dense_rank],
ntile(3) over (partition by Department order by Salary desc) as [Ntile]
from Employee  

Comments

No coments found to display!

Leave a Comment