Overview of the PERCENT_RANK() Function in SQL Server

In SQL Server, PERCENT_RANK() is a window function that calculates and returns the relative rank of a row within a group of rows, expressed as a value between 0 and 1.

In other words, it returns the percentage of partition values less than the value in the current row, excluding the highest value.

Syntax

The syntax goes like this:

PERCENT_RANK( )  
    OVER ( [ partition_by_clause ] order_by_clause )

So it doesn’t require or accept any arguments, but it does require an OVER clause with at least an ORDER BY clause.

Example

Here’s an example of using 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

Here, the PERCENT_RANK column displays the percentage rank of the ProductPrice column when sorted in ascending order.

We can see that the values range from 0 to 1. We can also see that two products have the same price (the left and right handed screwdrivers), and so they share the same percentage rank.

Partitioning the Result Set

We can use a PARTITION BY clause in order to partition the result set by a given column. For example, we can partition by vendor:

SELECT
    v.VendorName,
    p.ProductName,
    p.ProductPrice,
    PERCENT_RANK( ) OVER ( 
        PARTITION BY p.VendorId
        ORDER BY p.ProductPrice 
        ) AS PERCENT_RANK
FROM Products p 
INNER JOIN Vendors v
ON v.VendorId = p.VendorId;

Result:

VendorName     ProductName                      ProductPrice  PERCENT_RANK      
-------------  -------------------------------  ------------  ------------------
Mars Supplies  Long Weight (green)              11.99         0                 
Mars Supplies  Long Weight (blue)               14.75         0.3333333333333333
Mars Supplies  Left handed screwdriver          25.99         0.6666666666666666
Mars Supplies  Right handed screwdriver         25.99         0.6666666666666666
Randy Roofers  Sledge Hammer                    33.49         0                 
Pedal Medals   Hammock                          10            0                 
Pedal Medals   Straw Dog Box                    55.99         0.5               
Pedal Medals   Chainsaw                         245           1                 
Katty Kittens  Bottomless Coffee Mugs (4 Pack)  9.99          0                 
Katty Kittens  Tea Pot                          12.45         1

Here we get the percentage rank within each partition. Therefore the PERCENT_RANK column resets with each new partition.

In this case I used a SQL join to join the Products and Vendors table so that I could present the vendor names instead of just their IDs.

The `OVER` Clause is Required

We must provide the OVER clause when using the PERCENT_RANK() function. Here’s what happens if we remove it:

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.

The `ORDER BY` Clause is Required

Furthermore, the OVER clause must have at least an ORDER BY clause. Here’s what happens when we include an OVER clause without an ORDER BY clause:

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

Result:

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

Syntax

Example

Partitioning the Result Set

The OVER Clause is Required

The ORDER BY Clause is Required

The `OVER` Clause is Required

The `ORDER BY` Clause is Required