CODEWITHSUNDEEP
sqlselectgreatest-n-per-group
haha duang
haha duang

Reputation: 11

Find all the emps and their salaries who earn minimum salary in their department, display result in salary ascending order

employee database

SELECT name, MIN(salary)
FROM employee
GROUP BY deptid;

Why I can't select "name" here? And what is the suggested query for this question?

Upvotes: 0

Views: 603

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Why I can't select "name" here?

You can only use columns or expressions the the SELECT if they are present in the GROUP BY area, or part of a function that calculates an aggregation.

Think of grouping like buckets. When you say group by dept_id if there are 3 departments (even if they are mentioned 20 times; 20 employees in departments A,A,A,A,A,A,A,A,A,A,A,A,B,B,B,B,B,C,C,C) you get 3 buckets and there's a label on the outside of each bucket, one for each different value of dept_id (a bucket for A, a bucket for B and a bucket for C). Into those buckets, all the employees rows are thrown according to which department they're in: 12 rows in A, 5 rows in B, 3 rows in C. Then you say MIN(salary), the db searches each bucket looking for the minimum salary.

Why can't you say name? There simply isn't a bucket for it. Your buckets are labelled A, B and C for the departments. No bucket has a name written on it. While there are names inside the bucket, you can only ask for them in terms of a MIN, MAX, AVG, SUM, COUNT etc - it's the rule. "To get something out of a bucket you have to use some kind of function that calculates some statistic". Actually, in some DBs you can ask for a CSV string of all the names, but we'll ignore that because it's accessory to the main point: unless you use an aggregate function you can only ask for something written on the outside of a bucket

What information should the DB give you if you ask for name?

You could ask for MIN(name), but you'd only get one name. You could GROUP BY Name.. but then you'd have a lot more buckets, and your employees would be redistributed across the buckets. You would't be asking for the MIN salary per department bucket any more, you'd be asking for the min salary per name.. Not what you want

And what is the suggested query for this question?

Break it down:

"minimum salary in the department"

We're going to need a list of all departments and the minimum salary in the department

SELECT dept_id, MIN(salary) as min_sal_for_dept
FROM employee
GROUP BY dept_id

"Find all the emps .. who earn minimum salary in their department"

Now we know that the depatment ID and min salary is for that department, we can join it back to the employee data on the department id and the salary

SELECT * 
FROM
(
  SELECT dept_id, MIN(salary) as sal
  FROM employee
  GROUP BY dept_id
) dep_min_sals
INNER JOIN
employee e
ON
  e.dept_id = dep_min_sals.dept_id AND e.salary = dep_min_sals.sal

"display result in salary ascending order"

ORDER BY sal

The key thing to realize is that you have to group up (lose) detail in order to get the minimum salary per department, so if you want the detail back you have to join the grouped up data back to the detail. You cannot both lose the detail to calculate the minimum AND also keep the detail to have the employee name

Upvotes: 1

GMB
GMB

Reputation: 222482

A database-independent solution is to filter with a subquery:

select e.*
from emp e
where e.salary = (select min(e1.salary) from emp e1 where e1.deptid = e.deptid)
order by e.salary

Upvotes: 0

Related Questions