Which of the following is the correct syntax to select the top 3 salaries per department using ROW_NUMBER()?

easy📝 Syntax Q3 of 15

SQL - Advanced Query Patterns

Which of the following is the correct syntax to select the top 3 salaries per department using ROW_NUMBER()?

ASELECT department, employee, salary FROM (SELECT department, employee, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) AS ranked WHERE rn <= 3;

BSELECT department, employee, salary FROM employees WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) <= 3;

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

DSELECT department, employee, salary FROM employees ORDER BY salary DESC LIMIT 3 PARTITION BY department;

Step-by-Step Solution

Solution:

Step 1: Examine the window function and partitioning
The correct query uses a subquery to compute ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, numbering rows within each department by descending salary.
Step 2: Confirm the filtering mechanism
The outer query filters WHERE rn <= 3, selecting the top 3 rows per department.
Final Answer:
SELECT department, employee, salary FROM (SELECT department, employee, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) AS ranked WHERE rn <= 3; -> Option A
Quick Check:
Correct syntax uses ROW_NUMBER() with PARTITION BY and filter [OK]

Quick Trick: Use ROW_NUMBER() in a subquery, then filter on row number [OK]

Common Mistakes:

Using WHERE on ROW_NUMBER() directly without subquery
Missing PARTITION BY in ROW_NUMBER()
Using LIMIT with PARTITION BY incorrectly

Master "Advanced Query Patterns" 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

Which of the following is the correct syntax to select the top 3 salaries per department using ROW_NUMBER()?

Step 1: Examine the window function and partitioning

Step 2: Confirm the filtering mechanism

Final Answer:

Quick Check:

Want More Practice?