Confusion regarding Case statement evaluation

Question

I need a separate column containing the individual with the highest salary and lowest salary in each department. However, I'm only getting 'lowest salary' returned in that column for every record.

I realize this is probably very simple; I'm a brand-new user so go easy.

Below is the query that has gotten me closest to what I need.

SELECT department, first_name, salary,
CASE WHEN salary = MIN(salary) THEN 'lowest salary' 
     WHEN salary = MAX(salary) THEN 'highest salary'
    ELSE 'N/A'
END AS salary_by_department
FROM employees e1

WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = e2.department)
OR salary = (SELECT MIN(salary) FROM employees e2 WHERE e1.department = e2.department)

GROUP BY e1.department, e1.first_name, e1.salary;

Once again, expecting the output to be 'salary_by_department' column with highest and lowest-paid employee in each department, but I'm only getting 'lowest salary' returned for every record.

Please don't just give me the answer. I'm looking to thoroughly understand the logic so I can sort out these kinds of problems on my own.

Answer 1

If you only want to know the employees who have the minimum and maximum salary in a department, you can JOIN the employees table to a derived table of minimum and maximum salaries for each department:

SELECT e.department, e.first_name, e.salary,
       CASE WHEN e.salary = m.min_salary THEN 'lowest salary'
            WHEN e.salary = m.max_salary THEN 'highest salary'
       ELSE ''
       END AS salary_by_department
FROM employees e
JOIN (SELECT department, MIN(salary) AS min_salary, MAX(salary) AS max_salary
      FROM employees
      GROUP BY department) m ON m.department = e.department AND (m.min_salary = e.salary OR m.max_salary = e.salary)

If you want to get a list of all employees in the department with the note about highest and lowest salary where appropriate, change the JOIN to a LEFT JOIN:

SELECT e.department, e.first_name, e.salary,
       CASE WHEN e.salary = m.min_salary THEN 'lowest salary'
            WHEN e.salary = m.max_salary THEN 'highest salary'
       ELSE ''
       END AS salary_by_department
FROM employees e
LEFT JOIN (SELECT department, MIN(salary) AS min_salary, MAX(salary) AS max_salary
           FROM employees
           GROUP BY department) m ON m.department = e.department AND (m.min_salary = e.salary OR m.max_salary = e.salary)

Demo on dbfiddle

Note this version will work on versions of MySQL that don't have window functions (i.e. pre 8.0).

Answer 2

When you group by department, first_name and salary you basically get 2 rows - one with minimum salary and the other with max salary and assuming there are no employee with same salary in the same department.otherwise you will get more than 2 records. Now for each of this rows the min and the max salary is same as the salary. And since the lowest case expression is the first one to evalute you get all the rows as lowest salary.

With employees 
as(
Select 10 department, 'Adam' first_name, 100 salary union all
Select 10 department, 'God' first_name, 110  salary union all
Select 10 department, 'Nik' first_name, 90 salary union all
Select 20 department, 'Fos' first_name, 110  salary union all
Select 20 department, 'Loke' first_name, 210  salary union all
Select 20 department, 'Fos' first_name, 130  salary union all
Select 20 department, 'Duku' first_name, 170  salary 
)
SELECT department, first_name, salary,MIN(salary) min_sal,MAX(salary) max_sal
--,CASE WHEN salary = MIN(salary) THEN 'lowest salary' 
--     WHEN salary = MAX(salary) THEN 'highest salary'
--    ELSE 'N/A'
--END AS salary_by_department
FROM employees e1
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = 
e2.department)
OR salary = (SELECT MIN(salary) FROM employees e2 WHERE e1.department = 
e2.department)
GROUP BY e1.department, e1.first_name, e1.salary;

Here's the o/p

 department first_name  salary  min_sal max_sal
 10          God         110    110      110
 10          Nik         90     90       90
 20          Fos         110    110      110
 20          Loke        210    210      210

To get what you want you can do the following

SELECT e1.department, e1.first_name, salary,min_sal,max_sal
,CASE WHEN salary = min_sal THEN 'lowest salary' 
     WHEN salary = max_sal THEN 'highest salary'
    ELSE 'N/A'
 END AS salary_by_department
FROM employees e1
JOIN (Select department,min(salary) min_sal, max(salary) max_sal from employees group 
by department) e2 on e1.department=e2.department
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = 
e2.department)
OR salary = (SELECT MIN(salary) FROM employees e2 WHERE e1.department = 
e2.department)
GROUP BY e1.department, e1.first_name, e1.salary,min_sal,max_sal

Answer 3

I would suggest window functions:

select e.*,
       (case when seqnum_asc = 1 then 'lowest salary'
             when seqnum_desc = 1 then 'highest salary'
        end) as salary_by_department
from (select e.*,
             row_number() over (partition by department order by salary asc) as seqnum_asc,
             row_number() over (partition by department order by salary asc) as seqnum_desc
      from employees e
     ) e
where 1 in (seqnum_asc, seqnum_desc);

Your query is a bit complicated to follow. The outer group by is of no use. This is because you have salary = min(salary), but salary is an aggregation key. Hence, there is only one value.

The reason that you get 'lowest salary' is because it is the first condition in the case expression -- and is always true (well, assuming salary is not null).

You could fix your code by using window functions:

SELECT department, first_name, salary,
       (CASE WHEN salary = MIN(salary) OVER (PARTITION BY department) THEN 'lowest salary' 
             WHEN salary = MAX(salary) OVER (PARTITION BY department) THEN 'highest salary'
             ELSE 'N/A'
        END) AS salary_by_department
FROM employees e1
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = e2.department) OR
      salary = (SELECT MIN(salary) FROM employees e2 WHERE e1.department = e2.department);

But this version is much more complicated than necessary.

Answer 4

I think what your trying to achieve here is to get the min() salary and max() salary of each firstname/department.

Here's some suggestions.

1. is to left join first the min() salary of each firstname/department to employee table
2. left join the max() salary of each firstname/department to employee table
3. apply your case statement based on minsalary and maxsalary joins

select t1.department, t1.first_name, t1.salary
    , case when coalesce(minSalary.salary, 0) > 0 then 'lowest salary' 
        when coalesce(maxSalary.salary, 0) > 0 then 'highest salary'
    else 'N/A' end as salary_by_department
from
employees t1
left join
    (select department, first_name, min(salary) as salary
    from employees
    group by department, first_name) minSalary on minSalary.first_name = t1.first_name and t1.department = minSalary.department
left join
    (select department, first_name, max(salary) as salary
    from employees
    group by department, first_name) maxSalary on maxSalary.first_name = t1.first_name and t1.department = maxSalary.department
group by t1.department, t1.first_name, t1.salary

Answer 5

If you are using MySQL 8+, then ROW_NUMBER provides one nice solution:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) rn_min,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) rn_max
    FROM employees
)

SELECT
    department,
    MAX(CASE WHEN rn_min = 1 THEN first_name END) AS first_name_min,
    MAX(CASE WHEN rn_min = 1 THEN salary END) AS salary_min,
    MAX(CASE WHEN rn_max = 1 THEN first_name END) AS first_name_max,
    MAX(CASE WHEN rn_max = 1 THEN salary END) AS salary_max
FROM cte
WHERE 1 IN (rn_min, rn_max)
GROUP BY
    department
ORDER BY
    department;