Can I group by in SQL query with window function?

Question

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"

Answer 1

First thing to remember is that windowed functions (like OVER() clause) work on the result of the query. That is: Server first executes the query and only then applies the windowed function as defined by you (of course, it's an oversimplification of what actually happens, but good enough to illustrate my point).

This means that you can actually use windowed function and group by clause in the same query, but you need to encapsulate group by aggregate with windowed function aggregate, like this:

SELECT department_id,
       min(min(salary)) OVER (partition by department_id) as minsalary
FROM employees
GROUP BY department_id;

However, I agree that this is not a good place to use windowed function. Matt's proposition - which I upvoted, full disclosure - is best here (ROW_NUMBER() in CTE or subquery, then selecting only the desired rows in main SELECT).