Rank vs DenseRank Vs RowNumber

In SQL, RANK() and DENSE_RANK() are window functions that assign a rank to each row within a result set based on the values in one or more columns. While they serve a similar purpose, there is a key difference between them, particularly in how they handle ties (cases where multiple rows have the same values).

   
      

SELECT
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM
    Employee;

🔸If multiple rows have the same values, they receive the same rank, and the next rank is skipped.

Dense Rank

   
      

SELECT
    EmployeeID,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM
    Employee;

🔸However, in the case of ties, DENSE_RANK() does not skip ranks. It assigns the same rank to tied rows, and the next rank is not skipped. The ranking is consecutive and unique, even in the presence of ties.