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

The RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).

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

Example of Error

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

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    RANK( )
FROM Products;

Result:

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

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

Solution

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

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

Result:

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

No error this time.

Note that the OVER clause must have an ORDER BY clause when used with window functions such as RANK(). Omitting the ORDER BY clause will result in another error.