Post
Ask Question

Difference Between ROW_NUMBER, RANK, and DENSE_RANK In SQL Server

To demonstrate the differences between ROW_NUMBER, RANK, and DENSE_RANK In SQL Server I have chosen an Employee table that has two employees with the same salary. The following three functions are required for an ORDER BY expression in the OVER clause:

Row_Number()
Rank()
Dense_Rank()
 
This tells the SQL Server in which order to apply the numbers generated by the window function. In this case, I am using salary column, which is not unique.
  1. -- create table  
  2. CREATE TABLE Employee  
  3. (  
  4. Names VARCHAR(10),  
  5. Salary INT  
  6. )  
  7. GO  
  8. -- insert data  
  9. INSERT INTO dbo.Employee  
  10. VALUES ('Rony',10000),('Joy',9000),('Devid',8000),('Warner',7000),('Elly',7000),('Frenil',6000)  
  11. GO  
  12.   
  13. select   
  14. *,  
  15. Row_Number() over (order by salary desc) RowNumber,  
  16. rank() over (order by salary desc) RankId,  
  17. dense_rank() over (order by salary desc) DenseRank from Employee   
SQL Server

Row_Number() will generate a unique number for every row, even if one or more rows has the same value.

RANK() will assign the same number for the row which contains the same value and skips the next number.

DENSE_RANK () will assign the same number for the row which contains the same value without skipping the next number.

To understand the above example, here I have given a simple explanation.

The Salary values were unique until the 4th row, and all three functions return 4 on row four. The 5th salary column has the same salary as row four so it’s a tie. ROW_NUMBER function does not care; it continues to provide unique incrementing numbers. RANK and DENSE_RANK both return four again because both rows  four and five are ranked the same.

Let's insert one more employee with the same salary. Employee Name is Tod and salary is 7000.

  2. ---Insert one more employee with same salry as Warner and Elly with 7000    
  3.   
  4. INSERT INTO dbo.Employee    
  5.   
  6. VALUES ('Tod',7000)    
  7.   
  8. GO   
SQL Server
 
Look at the rank function and see how many rows have the same value. Look at snippet 1 and snippet 2; you will get a clear vision that rank function has skipped 5 because only Warner and Elly have the same salary with 7000. Now  snippet 2 has three employees, Warner, Elly, and Tod, with a salary of 7000. Hence rank function has skipped the numbers 5 and 6 and generated  beginning from 7.
 
Dense_Rank function will assign the same rank for those rows which have the same values without the skipping next number.
 
I hope all three functions clear from above given example. If you have any query or question on raw_number(), Rank() or Dense_Rank() function feel free to write to me.
 
Thank you .... Hope you have enjoy reading my blog...