When using the NTILE() function in SQL Server, we must include an OVER clause clause with an ORDER BY clause.
If you’re getting error msg 4112 that reads “The function ‘NTILE’ must have an OVER clause with ORDER BY” when using the NTILE() function, it’s because, although you’re (correctly) including an OVER clause, you’re omitting 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 produces the error:
SELECT
VendorId,
ProductName,
ProductPrice,
NTILE( 3 ) OVER (
PARTITION BY VendorId
) AS NTILE
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'NTILE' must have an OVER clause with ORDER BY.
This error occurred because the OVER clause doesn’t have an ORDER BY clause.
We’ll still get the error if we put an ORDER BY clause at the end of the query, but not in the OVER clause:
SELECT
VendorId,
ProductName,
ProductPrice,
NTILE( 3 ) OVER (
PARTITION BY VendorId
) AS NTILE
FROM Products
ORDER BY ProductPrice;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'NTILE' must have an OVER clause with ORDER BY.
So the NTILE() function must have an OVER clause with its own ORDER BY clause.
Solution
To fix this issue, simply add an ORDER BY clause to the OVER clause:
SELECT
VendorId,
ProductName,
ProductPrice,
NTILE( 3 ) OVER (
PARTITION BY VendorId
ORDER BY ProductPrice
) AS NTILE
FROM Products;Result:
VendorId ProductName ProductPrice NTILE -------- ------------------------------- ------------ ----- 1001 Long Weight (green) 11.99 1 1001 Long Weight (blue) 14.75 1 1001 Left handed screwdriver 25.99 2 1001 Right handed screwdriver 25.99 3 1002 Sledge Hammer 33.49 1 1003 Hammock 10 1 1003 Straw Dog Box 55.99 2 1003 Chainsaw 245 3 1004 Bottomless Coffee Mugs (4 Pack) 9.99 1 1004 Tea Pot 12.45 2
This time the function worked as expected, without error.
In this example I also used the PARTITION BY clause to partition the result set, but this is optional. We can omit the PARTITION BY clause but we can’t omit the ORDER BY clause.