SQL Exercises: Highest salary drawar in a department

SQL SUBQUERY: Exercise-54 with Solution

From the following table, write a SQL query to find those employees who earn the highest salary in a department. Return department ID, employee name, and salary.

Sample table: employees

Sample Solution:

SELECT department_id, first_name || ' ' || last_name AS Employee_name, salary 
	FROM employees a
		WHERE salary = 
			(SELECT MAX(salary) 
				FROM employees 
			WHERE department_id = a.department_id);

Sample Output:

department_id	employee_name	salary
90		Steven King			24000.00
60		Alexander Hunold	9000.00
100		Nancy Greenberg		12000.00
30		Den Raphaely		11000.00
50		Adam Fripp		8200.00
80		John Russell		14000.00
0		Kimberely Grant		7000.00
10		Jennifer Whalen		4400.00
20		Michael Hartstein	13000.00
40		Susan Mavris		6500.00
70		Hermann Baer		10000.00
110		Shelley Higgins		12000.00

Code Explanation:

The said query in SQL that selects the "department_id", concatenated "first_name" and "last_name" as "Employee_name", and "salary" from the 'employees' table. It only selects the employees who have the maximum salary in their department. The subquery retrieves the maximum salary for each department, and the outer query filters the employees by comparing their salary with the maximum salary of their respective department.

Visual Presentation:

Alternative Solutions:

Using JOINs and Subqueries:

SELECT a.department_id, 
       a.first_name || ' ' || a.last_name AS Employee_name, 
       a.salary
FROM employees a
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary = b.max_salary;

Using a Window Function:

SELECT department_id, 
       first_name || ' ' || last_name AS Employee_name, 
       salary
FROM (
    SELECT department_id, 
           first_name, 
           last_name, 
           salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 1;

Using a Correlated Subquery with EXISTS:

SELECT a.department_id, 
       a.first_name || ' ' || a.last_name AS Employee_name, 
       a.salary
FROM employees a
WHERE EXISTS (
    SELECT 1
    FROM employees b
    WHERE b.department_id = a.department_id
    GROUP BY b.department_id
    HAVING MAX(b.salary) = a.salary
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display the details of departments managed by Susan.
Next SQL Exercise: Employees who did not have any job in the past.