I need to get employees with smallest salary in their departments I did it using anti join.
select emp.employee_id,emp.last_name,emp.salary,emp.department_id
from employees emp
left join employees sml
on sml.department_id = emp.department_id and sml.salary < emp.salary
where sml.employee_id is null and emp.department_id is not null
But I’ve been told that it’s possible to do it using window function using one select. However I can’t group it by department_id and use it at the same time. Is that a bug or me being stupid?
SELECT department_id,
min(salary) OVER (partition by department_id) as minsalary
FROM employees;
GROUP BY department_id
SQL Developer says 00979. 00000 – “not a GROUP BY expression”
Advertisement
Answer
If you run your second query without the group by – which you may have already tried, from the extra semicolon in what you posted – you’ll see that you get one row for every employee, each showing the minimum salary in their department. That minimum is the analytic min() because it has a window clause. The PARTITION BY is the equivalent of a GROUP BY, but without the aggregation over the whole result set.
The simplest way to get the same result (almost) is to use the RANK() analytic function instead, which ranks the values based on the partition and order you supply, while allowing for ties:
SELECT employee_id, last_name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rnk
FROM employees
ORDER BY department_id, rnk;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID RNK
----------- ------------------------- ---------- ------------- ----------
200 Whalen 4400 10 1
202 Fay 6000 20 1
201 Hartstein 13000 20 2
119 Colmenares 2500 30 1
118 Himuro 2600 30 2
117 Tobias 2800 30 3
116 Baida 2900 30 4
115 Khoo 3100 30 5
114 Raphaely 11000 30 6
...
102 De Haan 17000 90 1
101 Kochhar 17000 90 1
100 King 24000 90 3
...
For departments 20 and 30 you can see the row ranked 1 is the lowest salary. For department 90 there are two employees ranked 1, because they have the same lowest salary.
You can use that as an inline view and select just those rows ranked number 1:
SELECT employee_id, last_name, salary, department_id
FROM (
SELECT employee_id, last_name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rnk
FROM employees
)
WHERE rnk = 1
ORDER BY department_id;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
202 Fay 6000 20
119 Colmenares 2500 30
203 Mavris 6500 40
132 Olson 2100 50
107 Lorentz 4200 60
204 Baer 10000 70
173 Kumar 6100 80
101 Kochhar 17000 90
102 De Haan 17000 90
113 Popp 6900 100
206 Gietz 8300 110
178 Grant 7000
13 rows selected.
If you didn’t have to worry about ties there is an even simpler alternative, but it ins’t appropriate here.
Notice that this gives you one more row than your original query. You are joining on sml.department_id = emp.department_id. If the department ID is null, as it is for employee 178, that join fails because you can’t compare null to null with equality tests. Because this solution doesn’t have a join, that doesn’t apply, and you see that employee in the results.