/*Over(partition by)*/ /*inner query*/

/*Over(partition by)*/

        select employee_id, last_name, department_name, salary

from (    

        select employee_id, last_name,salary, department_name, max(salary) over (partition by d.department_id) as maxsal

        from employees e join departments d

        on e.department_id=d.department_id

      )

where maxsal=salary

order by employee_id asc;

/*inner query*/

select employee_id, last_name, department_name, salary

from employees e, departments d

where e.department_id=d.department_id 

and (d.department_id, salary) in (

                                    select department_id, max(salary)

                                    from employees

                                    group by department_id

                                  )

order by employee_id asc;