PostgreSQL Aggregate Functions and Group By: Find the manager ID and the salary of the lowest-paid employee under that manager

9. Write a query to find the manager ID and the salary of the lowest-paid employee under that manager.

Sample Solution:

Code:

-- This SQL query retrieves the minimum salary for each manager, sorted in descending order of minimum salary.

SELECT manager_id, MIN(salary) -- Selects the manager_id and the minimum salary for each manager
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE manager_id IS NOT NULL -- Filters the rows to include only those where manager_id is not null, i.e., where an employee is a manager
GROUP BY manager_id -- Groups the results by manager_id, so that the minimum salary is calculated for each unique manager_id
ORDER BY MIN(salary) DESC; -- Orders the results in descending order based on the minimum salary

Explanation:

• The SQL query retrieves information about the minimum salary for each manager.
• The SELECT statement selects both the manager_id and the minimum salary for each manager.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The WHERE clause filters the rows to include only those where manager_id is not null, ensuring that only employees who are managers are included in the result set.
• The GROUP BY clause groups the results by manager_id, ensuring that the minimum salary is calculated for each unique manager.
• The ORDER BY clause orders the results in descending order based on the minimum salary, so managers with the highest minimum salary appear first in the result set.

Sample table: employees

Output:

pg_exercises=# SELECT manager_id, MIN(salary)
pg_exercises-# FROM employees
pg_exercises-# WHERE manager_id IS NOT NULL
pg_exercises-# GROUP BY manager_id
pg_exercises-# ORDER BY MIN(salary) DESC;
 manager_id |   min
------------+----------
          0 | 24000.00
        102 |  9000.00
        205 |  8300.00
        145 |  7000.00
        146 |  7000.00
        108 |  6900.00
        147 |  6200.00
        149 |  6200.00
        148 |  6100.00
        201 |  6000.00
        100 |  5800.00
        101 |  4400.00
        103 |  4200.00
        123 |  2500.00
        124 |  2500.00
        114 |  2500.00
        120 |  2200.00
        122 |  2200.00
        121 |  2100.00
(19 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to get the difference between the highest and lowest salaries.
Next: Write a query to get the department ID and the total salary payable in each department.