SQL SERVER – 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
GO
SELECT SalesOrderID, OrderQty,
RANK() OVER(ORDER BY SalesOrderID) 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 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
GO
SELECT SalesOrderID, OrderQty, ProductID,
RANK() OVER(PARTITION BY SalesOrderID
ORDER BY ProductID ) 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

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)

About these ads

8 thoughts on “SQL SERVER – Introduction to PERCENT_RANK() – Analytic Functions Introduced in SQL Server 2012

  1. Pingback: SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority « Journey to SQLAuthority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  3. Hi Pinal,

    Can you please explain the second example with partition…From what I understand from the Microsoft page Example A: http://msdn.microsoft.com/en-us/library/ms176102.aspx : in your example if they are partitioned by sales order id then all sales order id with equal value should be partitioned under one box and then rank function is applied within the particular partition and so on…Am I wrong on this ? If yes can you explain how exactly the ranking function works…Also from the Microsoft link I provided can you please explain the Example B ? How was the rank of second entity 4 ?

    Like

    • Great, Pinal! The formula for calculating the Percent_Rank( ) function helped me.

      Vivek, for the last question in your post, I think that the rank was done on the entire result set of the 290 records. The 2nd row ranks 4th. Then the TOP 10 clause is applied.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s