If you’re getting SQL Server error 10753 that reads “The function ‘LAST_VALUE’ must have an OVER clause”, it’s probably because you’re calling the LAST_VALUE() function without an OVER clause.
The LAST_VALUE() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, include an OVER clause when calling the LAST_VALUE() function.
Example of Error
Here’s an example of code that produces the error:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice )
FROM Products;Result:
Msg 10753, Level 15, State 1, Line 5 The function 'LAST_VALUE' must have an OVER clause.
In this case I called the LAST_VALUE() function without an OVER clause, which resulted in an error.
Solution
To fix this issue, add an OVER clause to the LAST_VALUE() function:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice ) OVER (
ORDER BY ProductPrice
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LAST_VALUE
FROM Products;Result:
VendorId ProductName ProductPrice LAST_VALUE -------- ------------------------------- ------------ ---------- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 245 1003 Hammock 10 245 1001 Long Weight (green) 11.99 245 1004 Tea Pot 12.45 245 1001 Long Weight (blue) 14.75 245 1001 Left handed screwdriver 25.99 245 1001 Right handed screwdriver 25.99 245 1002 Sledge Hammer 33.49 245 1003 Straw Dog Box 55.99 245 1003 Chainsaw 245 245
No more error.
It’s also important to remember that the OVER clause must have an ORDER BY clause. Omitting this will cause another error.
Also, in this example I used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in my OVER clause to ensure that the window frame extended to the last row of the query result set.