SQL-ROW_NUMBER() vs RANK() vs and DENSE_RANK()

The functions ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions in SQL Server (and other databases) that are used to assign a ranking or numbering to rows within a partition. While they seem similar, they have distinct behaviors.

1. ROW_NUMBER() Function

• Description - Assigns a unique sequential integer to rows within a partition, starting at 1 for the first row in each partition.
• Behavior - Always increments by 1 for each row, regardless of duplicates in the ORDER BY column(s).
• Use Case -  When you need a unique number for each row.

Syntax

Select ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column)

Example

SELECT 
    EmployeeID, 
    Salary, 
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

Result

2. RANK() Function

• Description - Assigns a rank to each row within a partition, with ranks increasing for each unique value in the ORDER BY column(s). Rows with duplicate values receive the same rank, and the next rank is skipped.
• Behavior - Skips ranks for duplicate values.
• Use Case - When ranking with gaps for ties is acceptable or required.

Syntax

Select RANK() OVER (PARTITION BY partition_column ORDER BY order_column)

Example

SELECT 
    EmployeeID, 
    Salary, 
    RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

Result

3. DENSE_RANK() Function

• Description - Assigns a rank to each row within a partition, like RANK(), but does not skip ranks for duplicate values.
• Behavior - No gaps in the ranking sequence.
• Use Case - When ranking without gaps for ties is required.

Syntax

Syntax

Select DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column)

Example

SELECT 
    EmployeeID, 
    Salary, 
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

Result

Key Differences