Difference between RANK, DENSE_RANK and ROW_NUMBER in SQL

Previously We discussed that How to Use  Dense_Rank() function to find Nth highest salary in sql server.

Now we are going to discuss What is difference between 

             Dense_Rank()     &      Rank()     &     Row_Number()

As I told you that Dense_Rank() create  one temporary  run time column which numbers the Salary according to the Rank priority like (1,2,3.....).

Rank() and  Row_Number() do the same thing but in different Ways. How?

Lets discuss- 

1-  Rank()-  It generate Temporary column at rum time, and do the numbering for every distinct row ,but it contains gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Rank() over(order by Salary desc) from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query give sequence (1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary, but it have Sequence 3 instead of 2 in Column RK because there are two members (Carry and Paul) having same salary.

There could be possible that 3 Members have highest salary (10000) then first three members will get sequence (1) and John will get 4 in RK column.

That’s why we mentioned above that function Rank() contains gaping in sequencing number.


2-Dense_Rank()-It generate Temporary column at rum time, and do the numbering for every distinct row ,but it doesn't contain gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Dense_Rank() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query  give sequence(1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary , it have Sequence 2  there is no gaping in Sequencing number

That’s why we mentioned above  that function  Dense_Rank() doesn't contain gaping in sequencing number.


3- Row_Number() – It also generate Temporary column at rum time, but the main thing is that it do numbering for every individual row without any gaping in sequencing number.

Let’s understand with query and Example:

select Employee_Name,Salary, Row_Number() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) but they have different Sequence(1,2) .

That’s why we mentioned above  that  function  Row_Number() do numbering for every individual row without any gaping in sequencing number.

Difference in a Glance – 

 

Author-Er. Rahul Kr. Yadav