[Solved] Identify the error in this query that attempts to select the top 1 salary per department:SELECT department, employee, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn... | SQL

SQL - Advanced Query Patterns

Identify the error in this query that attempts to select the top 1 salary per department:

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

AMissing GROUP BY clause

BROW_NUMBER() cannot be used with PARTITION BY

CCannot use alias rn in WHERE clause directly

DORDER BY salary DESC is invalid syntax

Step-by-Step Solution

Solution:

Step 1: Understand alias scope in SQL
Aliases like rn defined in SELECT cannot be used in WHERE clause directly.
Step 2: Correct approach
Use a subquery or CTE to assign rn, then filter in outer query.
Final Answer:
Cannot use alias rn in WHERE clause directly -> Option C
Quick Check:
Alias in SELECT not usable in WHERE [OK]

Quick Trick: Filter on ROW_NUMBER() alias in outer query, not WHERE [OK]

Common Mistakes:

Master "Advanced Query Patterns" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Identify the error in this query that attempts to select the top 1 salary per department: