If you’re getting an error that reads “The function ‘LAG’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the LAG() function without an OVER clause.
The LAG() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, be sure to include an OVER clause when calling the LAG() function.
Example of Error
Here’s an example of code that produces the error:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice )
FROM Products;Result:
Msg 10753, Level 15, State 1, Line 5 The function 'LAG' must have an OVER clause.
Here I called the LAG() function without an OVER clause, which resulted in an error.
Solution
To fix this issue, add an OVER clause to the LAG() function:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice ) OVER (
ORDER BY ProductPrice
) AS LAG
FROM Products;Result:
VendorId ProductName ProductPrice LAG -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 null 1003 Hammock 10 9.99 1001 Long Weight (green) 11.99 10 1004 Tea Pot 12.45 11.99 1001 Long Weight (blue) 14.75 12.45 1001 Left handed screwdriver 25.99 14.75 1001 Right handed screwdriver 25.99 25.99 1002 Sledge Hammer 33.49 25.99 1003 Straw Dog Box 55.99 33.49 1003 Chainsaw 245 55.99
This time we get the expected result.
It’s important to remember that the OVER clause must have an ORDER BY clause. Omitting this will cause another error.