Introduction to PERCENT_RANK() – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes.

Now let’s have fun following query:

USE AdventureWorks
GOSELECT SalesOrderID, OrderQty,RANK() OVER(ORDER BY SalesOrderID) Rnk,PERCENT_RANK() OVER(ORDER BY SalesOrderID) AS PctDistFROM Sales.SalesOrderDetailWHERE SalesOrderID IN (43670, 43669, 43667, 43663)ORDER BY PctDist DESCGO

The above query will give us the following result:

Now let us understand the resultset. You will notice that I have also included the RANK() function along with this query. The reason to include RANK() function was as this query is infect uses RANK function and find the relative standing of the query.

The formula to find PERCENT_RANK() is as following:

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

If you want to read more about this function read here.

Now let us attempt the same example with PARTITION BY clause

USE AdventureWorks
GOSELECT SalesOrderID, OrderQty, ProductID,RANK() OVER(PARTITION BY SalesOrderIDORDER BY ProductID ) Rnk,PERCENT_RANK() OVER(PARTITION BY SalesOrderIDORDER BY ProductID ) AS PctDistFROM Sales.SalesOrderDetail sWHERE SalesOrderID IN (43670, 43669, 43667, 43663)ORDER BY PctDist DESCGO

Now you will notice that the same logic is followed in follow result set.

I have now quick question to you – how many of you know the logic/formula of PERCENT_RANK() before this blog post?

Reference: Pinal Dave (http://blog.SQLAuthority.com)