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

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

To fix this issue, be sure to include an OVER clause when calling the PERCENT_RANK() function.

Example of Error

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

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

Result:

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

Here I forgot to include the OVER clause when calling the PERCENT_RANK() function, which resulted in an error.

Solution

To fix this issue, we must include an OVER clause when calling the PERCENT_RANK() function:

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                 

This time we get the expected result.

As mentioned, the OVER clause needs to have an ORDER BY clause. Omitting this will cause another error.