PERCENT_RANK is a new function introduced in SQL Server 2012. The function calculates the relative rank of a row within a subset of rows or in simpler terms it shows the percentage of values that are less than or equal to the current value.
The syntax for the function is as follows
PERCENT_RANK( ) OVER ( [ partition_by_clause ] order_by_clause )
Let us quickly implement PERCENT_RANK and understand how it works.
Use the following code to create a database with values in it.
USE [Master] GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'PERCENTRANK') BEGIN DROP DATABASE PERCENTRANK; END GO CREATE DATABASE PERCENTRANK; GO USE PERCENTRANK GO CREATE TABLE PERCENTRANKDEMO ( [ProductId] int, [SalesQty] int, [Year] int ); insert into PERCENTRANKDEMO VALUES (10,1000,2005),(20,1215,2005),(30,3327,2005), (10,2000,2006),(20,2415,2006),(30,3429,2006), (10,3050,2007),(20,3216,2007),(30,3737,2007), (10,3026,2008),(20,1618,2008),(30,3452,2008), (10,2004,2009),(20,4315,2009),(30,5435,2009), (10,3040,2010),(20,4015,2010),(30,4343,2010), (10,3060,2011),(20,4345,2011),(30,3237,2011), (10,3075,2012),(20,4035,2012),(30,7450,2012), (10,3055,2013),(20,5012,2013),(30,3321,2013), (10,3059,2014),(20,6019,2014),(30,3245,2014), (10,3150,2015),(20,6815,2015),(30,4000,2015);
Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.
SELECT ProductId,SalesQty,Year, PERCENT_RANK() OVER ( ORDER BY [SalesQty] ) AS PercentRank FROM PERCENTRANKDEMO WHERE productid = 30;
The outcome would be as below. The column PercentRank shows the percent of values that are less or equal to the current value of the SalesQty column. The first row in any set has a PERCENT_RANK of 0. That’s how it has been designed by default. In the result set 0.1 denotes 10 percent while 1 denotes 100 percent.