When using window functions such as RANK() in SQL Server, we must provide an OVER clause clause with an ORDER BY clause.

If you’re getting an error that reads “The function ‘RANK’ must have an OVER clause with ORDER BY”, it’s probably because you’re including an OVER clause with the RANK() function (as is required), but 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,
    RANK( ) OVER ( 
        PARTITION BY VendorId
        ) AS RANK
FROM Products;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'RANK' must have an OVER clause with ORDER BY.

This error occurred because I called the RANK() function without an ORDER BY clause. Specifically, I didn’t put an ORDER BY clause in the OVER 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,
    RANK( ) OVER ( 
        PARTITION BY VendorId
        ) AS RANK
FROM Products
ORDER BY ProductPrice;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'RANK' must have an OVER clause with ORDER BY.

As the error message states, the RANK() function must have an OVER clause with ORDER BY.

Solution

To fix this issue, add an ORDER BY clause to the OVER clause:

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

Result:

VendorId  ProductName                      ProductPrice  RANK
--------  -------------------------------  ------------  ----
1001      Long Weight (green)              11.99         1   
1001      Long Weight (blue)               14.75         2   
1001      Left handed screwdriver          25.99         3   
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.

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.