If you’re getting an error message that reads “The function ‘LEAD’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause when using the LEAD() function.
The LEAD() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
Example of Error
Here’s an example of code that results in the error:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 1 ) OVER ( ) AS LEAD
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'LEAD' must have an OVER clause with ORDER BY.
We get error message 4112. This error occurred because, although I provided an OVER clause, it doesn’t contain an ORDER BY clause.
Note that simply adding an ORDER BY clause to the end of the query does not address the issue:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 1 ) OVER ( ) AS LEAD
FROM Products
ORDER BY ProductPrice;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'LEAD' must have an OVER clause with ORDER BY.
While there’s no problem with having an ORDER BY clause at the end of the query, there still needs to be one in the OVER clause.
Solution
To fix this error, all we need to do is add an ORDER BY clause to the OVER clause:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 1 ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
VendorId ProductName ProductPrice LEAD -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 10 1003 Hammock 10 11.99 1001 Long Weight (green) 11.99 12.45 1004 Tea Pot 12.45 14.75 1001 Long Weight (blue) 14.75 25.99 1001 Left handed screwdriver 25.99 25.99 1001 Right handed screwdriver 25.99 33.49 1002 Sledge Hammer 33.49 55.99 1003 Straw Dog Box 55.99 245 1003 Chainsaw 245 null
No error. The function worked as expected.
The OVER clause can also have a PARTITION BY clause, but this is optional. We can omit the PARTITION BY clause, but not the ORDER BY clause.