SQL Programming Resources on Telegram: “SQL Interview Series

SQL Interview Series — Part 4

📌 Question 4: Find the Highest Salary in Each Department

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• department

• salary

Example:

employee_id | employee_name | department | salary
------------|---------------|------------|-------
1           | Amit          | IT         | 60000
2           | Rahul         | IT         | 90000
3           | Priya         | HR         | 50000
4           | Neha          | HR         | 70000
5           | Raj           | Finance    | 85000
6           | Karan         | Finance    | 95000

❓ Find the employee(s) with the highest salary in each department.

💡 Approach

We need to find the maximum salary separately for every department.

A common approach is to use a window function.

"RANK()" assigns a ranking within each department.

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM Employee
) e
WHERE rnk = 1;

🔎 How does it work?

"PARTITION BY department"

→ Creates a separate ranking for each department.

"ORDER BY salary DESC"

→ Places the highest salary first.

"RANK()"

→ Gives the highest salary a rank of 1.

Finally:

WHERE rnk = 1

→ Keeps only the highest-paid employee(s).

Expected result:

employee_name | department | salary
--------------|------------|-------
Rahul         | IT         | 90000
Neha          | HR         | 70000
Karan         | Finance    | 95000

⚠️ Why use "RANK()"?

If two employees have the same highest salary, both will receive rank 1 and both will be returned.

For example:

IT

Rahul 90000

Amit 90000

Raj 80000

Both Rahul and Amit will be returned.

Double Tap ❤️ For More