AdvancedWHERE & Filtering

Find employees whose salary rank in dept is in top 3

The query

SQL

SELECT * FROM (
  SELECT
    first_name, last_name, salary, dept_id,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

Tested against PostgreSQL 16

Note

“Window function in subquery to filter by rank — cannot use WHERE on window functions directly.

Tables referenced

employees10 cols