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
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.
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
DROP DATABASE PERCENTRANK;
CREATE DATABASE PERCENTRANK;
CREATE TABLE PERCENTRANKDEMO
insert into PERCENTRANKDEMO
Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.
PERCENT_RANK() OVER ( ORDER BY [SalesQty] )
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.