2

I have a sql query that outputs employees who have the minimum salary across departments. It does so by finding the minimum salary for each department and finding people with salaries that match ANY of those. In this case, Person 1 can belong to department A and have a salary of 70k (even though her department's minimum is 45k) and be returned in the query if another department's minimum salary is 70k. However, what if I instead want to output names of people who have the minimum salary for their respective department (so Person 1 would not be returned anymore). Here is the current sql query:

SELECT first_name, last_name, salary, department_id  
FROM employees  
WHERE salary IN  
( SELECT MIN(salary)  
FROM employees  
GROUP BY department_id 
);
Jane Sully
  • 3,137
  • 10
  • 48
  • 87

2 Answers2

2

If you have MySQL 8.0 then the simplest approach is using window function:

SELECT *
FROM (SELECT *, RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r
      FROM tab) sub
WHERE r = 1;

Or:

SELECT  first_name, last_name, salary, department_id  
FROM employees e
WHERE (department_id,salary) IN  
      ( SELECT department_id, MIN(salary)  
        FROM employees
        GROUP BY department_id);
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    I am a SQL noob and found out from this that you can pass `tuples` with `IN`... this is more valuable than it seems – StonedTensor Jan 11 '22 at 05:52
1

Instead fo a IN clause you can also use an inner join 

SELECT first_name, last_name, salary, department_id  
FROM employees  
INNER JOIN ( SELECT department_id, MIN(salary)  min_sal
FROM employees  
GROUP BY department_id 
) t on t.department_id =employees.department_id  
    and employees.salary = t.min_sal;

this should be better for performance

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Awesome! I like the inner join idea -- it is clear and intuitive. Can you explain what the upper and lowercase "T" represents in the query? Thanks! – Jane Sully Aug 21 '18 at 19:11
  • the t ( i have updated with lower case ... but mysq is case insensitive) .. is a table alias name .. the table name is required by from clause and i have use just the char t for the related table name alias ..hope is clear .. let me know – ScaisEdge Aug 21 '18 at 19:13
  • Awesome, and yes it is! – Jane Sully Aug 21 '18 at 19:20