Rank Employees using SQL Window Functions ROW_NUMBER RANK

Use Window Functions (ROW_NUMBER, RANK, DENSE_RANK)

Write a SQL query to rank employees within each department based on their salary using window functions.

Solution:

-- Rank employees within each department by salary.
SELECT 
    DepartmentID,
    EmployeeID,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankNum,
    DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use window functions (ROW_NUMBER, RANK, DENSE_RANK) to rank employees within each department based on their salary.

2. Key Components :

1. ROW_NUMBER() : Assigns a unique sequential number to rows within a partition.
2. RANK() : Assigns ranks with gaps for ties.
3. DENSE_RANK() : Assigns ranks without gaps for ties.
4. PARTITION BY : Divides data into groups (e.g., departments).
5. ORDER BY : Specifies the ranking order (e.g., descending salary).

3. Why Use Window Functions? :

1. Window functions allow you to perform calculations across a set of rows while retaining all rows in the result set.

4. Real-World Application :

1. For example, in HR systems, you might use this query to rank employees for performance reviews or promotions.

Additional Notes:

• Window functions are powerful tools for analytical queries without collapsing rows.
• Use this exercise to teach the differences between ROW_NUMBER, RANK, and DENSE_RANK.

For more Practice: Solve these Related Problems:

• Write a SQL query to rank employees within each department based on their years of experience using the RANK() function.
• Write a SQL query to assign a unique sequential number to employees within each job title using the ROW_NUMBER() function.
• Write a SQL query to rank products within each category by their sales volume using the DENSE_RANK() function.
• Write a SQL query to rank students within each class based on their GPA, ensuring no gaps in ranking for ties.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Advanced SQL Features Exercises Home
Next SQL Exercise: Create a Recursive Common Table Expression.