Query to display employees with lowest salary from each department

user_15753871 0
Viewed 1101

I want to display the lowest earning employees of each department based on salary using min().

I have tables of employees with id, first name, last name, department id, salary and departments, department_id, name department id from 1 to 5.

I am having trouble doing this, I only know how to start SELECT name, surname from employees WHERE...

greatest-n-per-group postgresql sql
2 Answers

You would use min() for this. You would use window functions:

select e.*
from (select e.*,
             rank() over (partition by department_id order by salary) as seqnum
      from employees e
     ) e
where seqnum = 1
order by department_id;
user_11440350

With rank()over() ranking window function you can have your rows within a group ranked as you wish. Here we have ranked all the employees starting with lowest salary in a department. Now if we select rows with rn =1 then it will select employees from a department with lowest salary.

Then joined the result with department table to get the name of the

With lowestEarningEmployees as
(
    id, first_name, last_name, department_id, salary, rank()over(partition by department_id order by salary)rn from employees 
)
select le.id, le.first_name, le.last_name, le.department_id,d.name, le.salary, 
from lowestEarningEmployees le inner join departments d on le.department_id=d.department_id

** If more than one employee in a department have lowest salary all of them will be selected. If you want to select only one employee with lowest salary then you need to use row_number() instead of rank().

You can also use subquery to have your desired result (Though I would suggest to use first one) :

Select e.id, e.first_name, e.last_name, e.department_id, d.name, e.salary 
from employees e inner join department d on e.department_id = d.department_id
where e.salary=(select min(salary) from employees empl where e.department_id=empl.department_id)
user_86516010
Related