> but if you want the highest paid employee per team or department That's straightforward, no? max(salary) over(partition by emp, dept order by salary desc

> but if you want the highest paid employee per team or department That's straightforward, no? max(salary) over(partition by emp, dept order by salary desc) as top_emp ... where top_emp = 1 (You need to put the window in a subquery)