SQL Server PERCENT_RANK Function

Summary: in this tutorial, you will learn how to use the SQL Server PERCENT_RANK() function to calculate the relative rank of a row within a group of rows.

SQL Server PERCENT_RANK() function overview

The PERCENT_RANK() function is similar to the CUME_DIST() function. The PERCENT_RANK() function evaluates the relative standing of a value within a partition of a result set.

The following illustrates the syntax of the SQL Server PERCENT_RANK() function:

PERCENT_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

PARTITION BY

The PARTITION BY clause distributes the rows into multiple partitions to which the PERCENT_RANK() function is applied. The PARTITION BY clause is optional. If you skip it, the function will treat the whole result set as a single partition.

ORDER BY

The ORDER BY clause specifies the logic order of rows in each partition. Because PERCENT_RANK() is order sensitive, the order_by_clause is required.

Return value

The result of PERCENT_RANK() is greater than 0 and less than or equal to 1.

0 < PERCENT_RANK() <= 1
Code language: SQL (Structured Query Language) (sql)

The first row has a rank value of zero. Tie values evaluate to the same cumulative distribution value.

The PERCENT_RANK() function includes NULL values by default and treats them as the lowest possible values.

SQL Server PERCENT_RANK() examples

Let’s take some examples of using the PERCENT_RANK() function.

The following statement creates a new view named sales.vw_staff_sales for the demonstration.

CREATE VIEW sales.vw_staff_sales(
    staff_id, 
    year, 
    net_sales
) AS
SELECT 
    staff_id, 
    YEAR(order_date), 
    ROUND(SUM(quantity*list_price*(1-discount)),0)
FROM 
    sales.orders o
INNER JOIN sales.order_items i on i.order_id = o.order_id
WHERE 
    staff_id IS NOT NULL
GROUP BY 
    staff_id, 
    YEAR(order_date);
Code language: SQL (Structured Query Language) (sql)

Using SQL Server PERCENT_RANK() function over a result set example

This example uses the PERCENT_RANK() function to calculate the sales percentile of each sales staff in 2016:

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    PERCENT_RANK() OVER (
        ORDER BY net_sales DESC
    ) percent_rank
FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR = 2016;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

To make the output more readable, you can use the FORMAT() function to format the percent rank in percentage (%):

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    FORMAT(
        PERCENT_RANK() OVER (
            ORDER BY net_sales DESC
        ) ,
    'P') percent_rank

FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR = 2016;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the new output:

Using SQL Server PERCENT_RANK() function over partitions example

The following example uses the PERCENT_RANK() to calculate the sales percentile for each staff in 2016 and 2017.

SELECT 
    year,
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    FORMAT(
        PERCENT_RANK() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) ,
    'P') percent_rank

FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR IN (2016,2017);
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• The PARTITION BYclause distributed the rows by year into two partitions, one for 2016 and the other for 2017.
• The ORDER BY clause sorted rows in each partition by net sales from high to low.
• The PERCENT_RANK() function is applied to each partition separately and recomputed the rank when crossing the partition’s boundary.

In this tutorial, you have learned how to use the SQL Server PERCENT_RANK() function to calculate the relative rank of a row within a group of rows.