[Solved] Why does this query produce incorrect last values?SELECT department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) FROM employees; | SQL

SQL - Advanced Window Functions

Why does this query produce incorrect last values?

SELECT department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) FROM employees;

ABecause ORDER BY must be DESC for LAST_VALUE

BBecause PARTITION BY is not allowed with LAST_VALUE

CBecause the default frame ends at current row, not the last row

DBecause salary is not aggregated

Step-by-Step Solution

Solution:

Step 1: Understand default frame behavior
LAST_VALUE defaults to frame from unbounded preceding to current row, so it returns current row's salary.
Step 2: Why this causes incorrect last values
To get the actual last salary in partition, frame must be extended to unbounded following.
Final Answer:
Because the default frame ends at current row, not the last row -> Option C
Quick Check:
LAST_VALUE default frame excludes rows after current [OK]

Quick Trick: Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for full frame [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Why does this query produce incorrect last values?