[Solved] Find the issue in this query:SELECT id, NTH_VALUE(salary, 3) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS third_salary FROM Employees; | SQL

SQL - Advanced Window Functions

Find the issue in this query:

SELECT id, NTH_VALUE(salary, 3) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS third_salary FROM Employees;

ANTH_VALUE cannot be used with ROWS frame

BThe window frame excludes preceding rows, so 3rd value may not exist

CORDER BY DESC is invalid inside OVER()

DThe query is correct and will return the 3rd highest salary for all rows

Step-by-Step Solution

Solution:

Step 1: Understand window frame boundaries
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING includes current and following rows only, excluding preceding rows.
Step 2: Impact on NTH_VALUE
Since frame starts at current row, for first rows frame may have fewer than 3 rows, so 3rd value may not exist, resulting in NULL.
Final Answer:
The window frame excludes preceding rows, so 3rd value may not exist -> Option B
Quick Check:
Window frame affects availability of nth value [OK]

Quick Trick: Window frame must include enough rows for nth value [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Find the issue in this query: