Forum

Forums
SQL
Interview Questions...
Write an SQL Query ...
 
Share:
Notifications
Clear all

Write an SQL Query to Find the Highest Salary in Each Department

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:27 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

How can you write an SQL query to find the highest salary in each department? Assume you have an employees table with columns employee_id, department, employee_name, and salary.

Answer:

To find the highest salary in each department, we can use the MAX function with the GROUP BY clause, or alternatively, we can use window functions like ROW_NUMBER() to get more detail on the employee with the highest salary in each department. Here’s how each approach works.

Solution 1: Using MAX and GROUP BY

Assume the employees table structure is as follows:

employee_id department employee_name salary
1 HR Alice 70000
2 IT Bob 90000
3 HR Carol 80000
4 IT Dave 85000
5 Sales Eve 75000

To get the highest salary in each department:

SQL Query:

sql
 
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

Explanation:

  1. MAX(salary): The MAX function calculates the highest salary within each group.
  2. GROUP BY department: The GROUP BY clause groups records by department, allowing MAX(salary) to return the highest salary in each department.

Expected Output:

department highest_salary
HR 80000
IT 90000
Sales 75000

Solution 2: Using ROW_NUMBER() to Find Employee with Highest Salary

If you want to retrieve the employee’s name and other details along with the highest salary in each department, ROW_NUMBER() can be helpful.

SQL Query:

sql
 
SELECT employee_id, department, employee_name, salary
FROM (
SELECT employee_id, department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
) AS ranked_salaries
WHERE rank = 1;

Explanation:

  1. ROW_NUMBER() with PARTITION BY: The ROW_NUMBER() function assigns a unique rank to each employee within their department based on salary in descending order.
  2. WHERE rank = 1: This filter ensures we only retrieve the top-ranked (highest-paid) employee in each department.

Expected Output:

employee_id department employee_name salary
3 HR Carol 80000
2 IT Bob 90000
5 Sales Eve 75000

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: