If you’re getting error message 4112 that reads “The function ‘FIRST_VALUE’ must have an OVER clause with ORDER BY” when using the FIRST_VALUE() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.
In SQL Server, the FIRST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.
To fix this error, simply add an ORDER BY clause to the OVER clause.
Example of Error
Here’s an example of code that produces the error:
SELECT
VendorId,
ProductName,
ProductPrice,
FIRST_VALUE( ProductPrice ) OVER ( )
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'FIRST_VALUE' must have an OVER clause with ORDER BY.
This error occurred because the OVER clause doesn’t have an ORDER BY clause.
Adding an ORDER BY clause to the end of the query is not sufficient though:
SELECT
VendorId,
ProductName,
ProductPrice,
FIRST_VALUE( ProductPrice ) OVER ( )
FROM Products
ORDER BY ProductPrice;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'FIRST_VALUE' must have an OVER clause with ORDER BY.
While it’s perfectly valid to have an ORDER BY clause at the end of the query, we still need to have one in the OVER clause.
Solution
To fix this issue, all we need to do is add an ORDER BY clause to the OVER clause:
SELECT
VendorId,
ProductName,
ProductPrice,
FIRST_VALUE( ProductPrice ) OVER (
ORDER BY ProductPrice
) AS FIRST_VALUE
FROM Products;Result:
VendorId ProductName ProductPrice FIRST_VALUE -------- ------------------------------- ------------ ----------- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 9.99 1003 Hammock 10 9.99 1001 Long Weight (green) 11.99 9.99 1004 Tea Pot 12.45 9.99 1001 Long Weight (blue) 14.75 9.99 1001 Left handed screwdriver 25.99 9.99 1001 Right handed screwdriver 25.99 9.99 1002 Sledge Hammer 33.49 9.99 1003 Straw Dog Box 55.99 9.99 1003 Chainsaw 245 9.99
No error this time. The function returned the first value as expected.
We can also use a PARTITION BY clause in the OVER clause, but this is optional. The ORDER BY clause however, is mandatory.