RANK() and DENSE_RANK() vs Row_Number() function in sql

Let’s understand the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() functions in SQL with an example.

Consider the following Employees table:

EmployeeID	Name	Salary
1	John	5000
2	Alice	5000
3	Bob	6000
4	David	7000
5	Ethan	8000
6	Frank	8000
7	Grace	9000

Now, let’s apply the RANK(), DENSE_RANK(), and ROW_NUMBER() functions on the Salary column:

SELECT 
    EmployeeID, 
    Name, 
    Salary,
    RANK() OVER (ORDER BY Salary) as Rank,
    DENSE_RANK() OVER (ORDER BY Salary) as Dense_Rank,
    ROW_NUMBER() OVER (ORDER BY Salary) as Row_Number
FROM Employees;

The output would be:

EmployeeID	Name	Salary	Rank	Dense_Rank	Row_Number
1	John	5000	1	1	1
2	Alice	5000	1	1	2
3	Bob	6000	3	2	3
4	David	7000	4	3	4
5	Ethan	8000	5	4	5
6	Frank	8000	5	4	6
7	Grace	9000	7	5	7

Here’s how each function works:

• RANK(): This function gives the same rank for equal values. Notice that both John and Alice have a rank of 1. The next rank, however, is 3 (not 2) because two rows have taken the first two ranks.
• DENSE_RANK(): This function also gives the same rank for equal values, but unlike RANK(), it does not skip any rank. So, after John and Alice, Bob gets a rank of 2.
• ROW_NUMBER(): This function simply gives a unique row number to each row irrespective of duplicates. So, even though John and Alice have the same salary, they get different row numbers.