You want to find the top 2 highest salaries per department from the employees table. Which query correctly achieves this using window functions?

hard📝 Application Q8 of 15

PostgreSQL - Window Functions in PostgreSQL

You want to find the top 2 highest salaries per department from the employees table. Which query correctly achieves this using window functions?

ASELECT * FROM employees ORDER BY salary DESC LIMIT 2

BSELECT * FROM employees WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) <= 2

CSELECT * FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department)

DSELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2

Step-by-Step Solution

Solution:

Step 1: Use ROW_NUMBER() to rank salaries per department
ROW_NUMBER() with PARTITION BY department and ORDER BY salary DESC assigns ranks starting at highest salary.
Step 2: Filter top 2 per department
Wrap in subquery and filter where rank <= 2 to get top 2 salaries per department.
Final Answer:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2 -> Option D
Quick Check:
Use subquery with ROW_NUMBER() and filter by rank [OK]

Quick Trick: Filter window function results in outer query for top N per group [OK]

Common Mistakes:

Master "Window Functions in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free