If you’re getting an error that reads “The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’re calling the ROW_NUMBER() function without an ORDER BY clause.
Window functions such as ROW_NUMBER() require an OVER clause, and that clause must have an ORDER BY clause. If you’re getting the above error, there’s a good chance you’re providing an OVER clause, but you’re omitting the ORDER BY clause.
To fix this issue, add an ORDER BY clause to the OVER clause when calling the ROW_NUMBER() function.
Example of Error
Here’s an example of code that produces the error:
SELECT
ROW_NUMBER( ) OVER (
PARTITION BY VendorId
) AS ROW_NUMBER,
VendorId,
ProductName,
ProductPrice
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 2 The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
This error occurred because I called the ROW_NUMBER() function without an ORDER BY clause.
More specifically, it happened because I didn’t put an ORDER BY clause in the OVER clause.
Solution
To fix this issue, add an ORDER BY clause to the OVER clause:
SELECT
ROW_NUMBER( ) OVER (
PARTITION BY VendorId
ORDER BY ProductPrice
) AS ROW_NUMBER,
VendorId,
ProductName,
ProductPrice
FROM Products;Result:
ROW_NUMBER VendorId ProductName ProductPrice ---------- -------- ------------------------------- ------------ 1 1001 Long Weight (green) 11.99 2 1001 Long Weight (blue) 14.75 3 1001 Left handed screwdriver 25.99 4 1001 Right handed screwdriver 25.99 1 1002 Sledge Hammer 33.49 1 1003 Hammock 10 2 1003 Straw Dog Box 55.99 3 1003 Chainsaw 245 1 1004 Bottomless Coffee Mugs (4 Pack) 9.99 2 1004 Tea Pot 12.45
This time the function worked as expected.
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.