If you’re getting an error that reads “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the ROW_NUMBER() function without an OVER clause.

Window functions such as ROW_NUMBER() require an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, add an OVER clause when calling the ROW_NUMBER() function.

Example of Error

Here’s an example of code that produces the error:

SELECT
    ROW_NUMBER( ),
    ProductName,
    ProductPrice
FROM Products;

Result:

Msg 10753, Level 15, State 3, Line 5
The function 'ROW_NUMBER' must have an OVER clause.

This error occurred because I called the ROW_NUMBER() function without an OVER clause.

Solution

To fix this issue, use an OVER clause when calling the ROW_NUMBER() function:

SELECT
    ROW_NUMBER( ) OVER ( 
        ORDER BY ProductPrice 
        ) AS ROW_NUMBER,
    ProductName,
    ProductPrice
FROM Products;

Result:

ROW_NUMBER  ProductName                      ProductPrice
----------  -------------------------------  ------------
1           Bottomless Coffee Mugs (4 Pack)  9.99        
2           Hammock                          10          
3           Long Weight (green)              11.99       
4           Tea Pot                          12.45       
5           Long Weight (blue)               14.75       
6           Left handed screwdriver          25.99       
7           Right handed screwdriver         25.99       
8           Sledge Hammer                    33.49       
9           Straw Dog Box                    55.99       
10          Chainsaw                         245         

This time the function worked as expected.

You might have noticed that I included an ORDER BY clause in my OVER clause. That’s because the OVER clause must have an ORDER BY clause when used with the ROW_NUMBER() function (and other window functions). Omitting the ORDER BY clause will result in another error.