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 (https://blog.sqlauthority.com)
10 Comments. Leave new
Thank you so much…….. sir
thank you so much
Thanku So much. Today i learn a new thing :)
Hi pinal
I would like to ask something that i can not display picture of your website.could you help me how can i display ?
Hi Pinal,
Can you please explain the second example with partition…From what I understand from the Microsoft page Example A: https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017 : 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 ?
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.
i find that your formula not match the example from msdn source.
url: https://docs.microsoft.com/en-us/sql/t-sql/functions/cume-dist-transact-sql?view=sql-server-2017
see the PctRank column.
Department LastName Rate CumeDist PctRank
———————- ———————- ——————— ———————- ———————-
Document Control Arifin 17.7885 1 1
Document Control Norred 16.8269 0.8 0.5
Document Control Kharatishvili 16.8269 0.8 0.5
Document Control Chai 10.25 0.4 0
Document Control Berge 10.25 0.4 0
Information Services Trenary 50.4808 1 1
Information Services Conroy 39.6635 0.9 0.888888888888889
Information Services Ajenstat 38.4615 0.8 0.666666666666667
Information Services Wilson 38.4615 0.8 0.666666666666667
Information Services Sharma 32.4519 0.6 0.444444444444444
Information Services Connelly 32.4519 0.6 0.444444444444444
Information Services Berg 27.4038 0.4 0
Information Services Meyyappan 27.4038 0.4 0
Information Services Bacon 27.4038 0.4 0
Information Services Bueno 27.4038 0.4 0
with all respect, i have a quick question, sir, did you really find the formula from
https://en.wikipedia.org/wiki/Percentile ?
this column.
PctRank
———————-
1
0.5
0.5
0
0
1
0.888888888888889
0.666666666666667
0.666666666666667
0.444444444444444
0.444444444444444
0
0
0
0
Frankly speeking. started to read in MSDN. After reading single lane, i immediatly switched to SQLAuthority…
Thank you for putting this together, you really are SQL Authority!