Published on

November 6, 2011

Understanding SQL Server’s PERCENT_RANK() Function

SQL Server 2012 introduced a new analytical function called PERCENT_RANK(). This function allows you to determine the relative standing of a value within a query result set or partition. In this blog post, we will explore the functionality of PERCENT_RANK() through a brief example.

For the purpose of this example, we will be using the AdventureWorks sample database, which is commonly used by developers for experimentation. Let’s dive into the following query:

USE AdventureWorks
GO

SELECT SalesOrderID, OrderQty, RANK() OVER (ORDER BY SalesOrderID) AS Rnk, 
       PERCENT_RANK() OVER (ORDER BY SalesOrderID) AS PctDist
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY PctDist DESC
GO

The above query will produce the following result:

SalesOrderIDOrderQtyRnkPctDist
43670541.000000
43669330.666667
43667220.333333
43663110.000000

Let’s analyze the result set. In addition to the PERCENT_RANK() function, I have also included the RANK() function in the query. The reason for this is that the PERCENT_RANK() function is based on the RANK() function and calculates the relative standing of the query. The formula to calculate PERCENT_RANK() is as follows:

PERCENT_RANK() = (RANK() - 1) / (Total Rows - 1)

If you want to learn more about this function, you can read the documentation here.

Now, let’s attempt the same example with the PARTITION BY clause:

USE AdventureWorks
GO

SELECT SalesOrderID, OrderQty, ProductID, RANK() OVER (PARTITION BY SalesOrderID ORDER BY ProductID) AS Rnk, 
       PERCENT_RANK() OVER (PARTITION BY SalesOrderID ORDER BY ProductID) AS PctDist
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY PctDist DESC
GO

You will notice that the same logic is applied in the resulting dataset. The PARTITION BY clause allows you to divide the result set into partitions based on a specific column, in this case, SalesOrderID. The PERCENT_RANK() function is then calculated within each partition, providing a relative standing for each value within its respective partition.

So, how many of you were familiar with the logic/formula of PERCENT_RANK() before reading this blog post? Understanding the functionality of SQL Server’s PERCENT_RANK() function can greatly enhance your analytical capabilities when working with query result sets.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.