Q48: How do you find the highest salary in EACH department?
Find the highest or Nth highest salary per group by pairing DENSE_RANK() or ROW_NUMBER() with PARTITION BY.
Top Earner per Department Query
WITH DeptRankedSalaries AS (
SELECT
department_id,
employee_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_rnk
FROM employees
)
SELECT department_id, employee_id, salary
FROM DeptRankedSalaries
WHERE dept_rnk = 1;💡 PARTITION BY resets rankings for each department independently, isolating top earners within their respective teams.
Key Takeaways
PARTITION BYcalculates rank ordering independently within each logical group.- Partitioned window functions avoid complex nested subqueries.