You want to select the top 2 highest paid employees per department from the employees table. Which query correctly uses ROW_NUMBER() to achieve this?

hard📝 Application Q15 of 15

SQL - Window Functions Fundamentals

You want to select the top 2 highest paid employees per department from the employees table. Which query correctly uses ROW_NUMBER() to achieve this?

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

BSELECT *, ROW_NUMBER() FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department);

CSELECT department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees WHERE rn <= 2;

DSELECT department, salary FROM employees ORDER BY salary DESC LIMIT 2;

Step-by-Step Solution

Solution:

Step 1: Use ROW_NUMBER() with PARTITION BY department
To get top 2 per department, assign row numbers restarting for each department ordered by salary descending.
Step 2: Filter rows with row number less than or equal to 2
Wrap the query as a subquery and filter where rn <= 2 to get top 2 employees 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 A
Quick Check:
ROW_NUMBER() + PARTITION + filter = top N per group [OK]

Quick Trick: Use ROW_NUMBER() with PARTITION BY and filter rn <= N [OK]

Common Mistakes:

Not using PARTITION BY to group by department
Filtering on rn in the same SELECT without subquery
Using LIMIT without partitioning

Master "Window Functions Fundamentals" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

You want to select the top 2 highest paid employees per department from the employees table. Which query correctly uses ROW_NUMBER() to achieve this?

Step 1: Use ROW_NUMBER() with PARTITION BY department

Step 2: Filter rows with row number less than or equal to 2

Final Answer:

Quick Check:

Want More Practice?