If you’re getting an error message that reads “The function ‘PERCENT_RANK’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause when using the PERCENT_RANK() function.

The PERCENT_RANK() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not 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,
    PERCENT_RANK( ) OVER (
     ) AS PERCENT_RANK
FROM Products;

Result:

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

We get error message 4112. This error occurred because, although I provided an OVER clause, it doesn’t contain an ORDER BY clause.

Note that simply adding an ORDER BY clause to the end of the query does not address the issue:

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

Result:

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

While there’s no problem with having an ORDER BY clause at the end of the query, there still needs to be one in the OVER clause.

Solution

To fix this error, all we need to do is add an ORDER BY clause to the OVER clause:

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

Result:

VendorId  ProductName                      ProductPrice  PERCENT_RANK      
--------  -------------------------------  ------------  ------------------
1004      Bottomless Coffee Mugs (4 Pack)  9.99          0                 
1003      Hammock                          10            0.1111111111111111
1001      Long Weight (green)              11.99         0.2222222222222222
1004      Tea Pot                          12.45         0.3333333333333333
1001      Long Weight (blue)               14.75         0.4444444444444444
1001      Left handed screwdriver          25.99         0.5555555555555556
1001      Right handed screwdriver         25.99         0.5555555555555556
1002      Sledge Hammer                    33.49         0.7777777777777778
1003      Straw Dog Box                    55.99         0.8888888888888888
1003      Chainsaw                         245           1                 

No error. The function worked as expected.

While we can add a PARTITION BY clause in the OVER clause when using window functions such as PERCENT_RANK(), this is optional. We can omit the PARTITION BY clause, but not the ORDER BY clause.