Rank Functions in SQL Server

  1. ROW_NUMBER() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE>): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition.
  2. RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >): Returns rank for rows within the partition of result set.
  3. DENSE_RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking.
  4. NTILE(INTEGER_EXPRESSION) OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups.
  1. --create Employee table  
  2. create table Employee  
  3. (  
  4.    EmpId int identity(1,1) primary key,  
  5.    FirstName varchar(100),  
  6.    LastName varchar(100),  
  7.    JoinDate datetime ,  
  8.    Salary int ,  
  9.    Department varchar(20)  
  10. )  
  1. --Insert data to Employee table  
  3. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Rakesh','Kalluri','2012-07-01 10:00:00.000',20000,'Software')  
  4. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Shabari','Vempati','2011-05-01 10:00:00.000',25000,'Software')  
  5. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Venkatesh','Bodupaly','2013-04-01 10:00:00.000',15000,'Bpo')  
  6. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Surjan','Peddineni','2011-07-01 10:00:00.000',25000,'Software')  
  7. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')  
  8. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')  
  9. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')  
  10. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')  
  11. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')  
  12. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')  
  13. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Anusha','Kumari','2011-07-01 10:00:00.000',35000,'Software')  
  14. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')  
  15. insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Radha','Kumari','2012-07-01 10:00:00.000',10000,'Bpo')  
  1. --selecting data from Employee table  
  2. select * from Employee  

  1. --Row_Number() with out using partition cluase  
  2. select * ,row_number() over (order by Salary descas Row_Num from Employee  

  1. --Row_Number() with using partition cluase  
  2. select * ,row_number() over (partition by Department order by Salary descas Row_Num from Employee  

  1. --rank() with out using partition cluase  
  2. select * ,rank() over (order by Salary descas [Rank] from Employee  

  1. --rank() with using partition cluase  
  2. select * ,rank() over (partition by Department order by Salary descas [Rank] from Employee  

  1. --dense_rank() with out using partition cluase  
  2. select * ,dense_rank() over (order by Salary descas [Dense_rank] from Employee  

  1. --dense_rank() with using partition cluase  
  2. select * ,dense_rank() over (partition by Department order by Salary descas [Dense_rank] from Employee  

  1. --ntile(input_exp) with out using partition cluase  
  2. select * ,ntile(3) over (order by Salary descas [ntile] from Employee  
In Ntile it accepts the input parameter based on input it divides the row ranking.

  1. --ntile(input_exp) with using partition cluase  
  2. select * ,ntile(3) over (partition by De6partment order by Salary descas [ntile] from Employee