DENSE_RANK function in SQL

The DENSE_RANK function in SQL is a window function that assigns a rank to each row within a partition of a result set. The rank is based on the values of one or more columns. Unlike the RANK function, which can leave gaps in the ranking after ties, DENSE_RANK does not skip ranks after a tie. Instead, it assigns consecutive ranks, ensuring no gaps in ranking values.

How DENSE_RANK Works

• Ties: When two or more rows tie for a rank based on the ordering column(s), they receive the same rank. The next rank(s) are incremented by 1, regardless of the number of tied rows.
• Partitioning: If the PARTITION BY clause is used, the ranking restarts for each partition.
• Ordering: The ORDER BY clause determines the order in which the ranks are assigned.

SQL Syntax

DENSE_RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3, column4, ...
) AS alias_name

Example Usage

Consider a table Employees with the following columns: EmployeeID, DepartmentID, and Salary.

CREATE TABLE Employees (
    EmployeeID int,
    DepartmentID int,
    Salary decimal
);

Suppose you want to rank employees within each department based on their salary in descending order:

SELECT
    EmployeeID,
    DepartmentID,
    Salary,
    DENSE_RANK() OVER (
        PARTITION BY DepartmentID
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM
    Employees;

Explanation of the Example

• PARTITION BY DepartmentID: The ranking is reset for each department.
• ORDER BY Salary DESC: Employees are ranked based on their salary, with higher salaries receiving a lower rank number.
• DENSE_RANK(): Employees with the same salary in the same department receive the same rank. The next different salary in the same partition gets the next consecutive rank.

Use Cases

• Performance Evaluation: Ranking employees by performance metrics within departments.
• Educational Grading: Assigning ranks to students based on scores, where students with identical scores receive the same rank.
• Sales Data Analysis: Ranking products or sales regions by sales volume or revenue.