The SQL PERCENT_RANK is one of the Analytic Function, which will calculate the relative rank of each row. This PERCENT_RANK function will return the rank from a range of values, which are greater than 0 and less than 1. The basic syntax of the PERCENT_RANK in SQL Server is:
SELECT PERCENT_RANK() OVER ( PARTITION_BY_Clause ORDER_BY_Clause ) FROM [Source]
We are going to use the below-shown data for this PERCENT_RANK demonstration
SQL PERCENT_RANK without Partition By Clause
In this example, we will show what will happen if we omit the Partition By Clause in the PERCENT_RANK Function in SQL Server.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENT_RANK() OVER (ORDER BY [Sales] ASC) AS PercentRank FROM [Employee]
OUTPUT
ANALYSIS
The Order By Clause Sort the Employee table Ascending order using their Sales Amount
ORDER BY [Sales] ASC
Next, the PERCENT_RANK function returns the percentage ranks as the output. Here, a row with the highest sales will assign 1 as the rank, and for the least sales 0 is the percentage rank
PERCENT_RANK() OVER (ORDER BY [Sales] ASC) AS PercentRank
Let me change the Order by clause from Ascending order to Descending. As you can see, it ranks the least value as 1, and the highest value as 0.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENT_RANK() OVER (ORDER BY [Sales] DESC) AS PercentRank FROM [Employee]
OUTPUT
SQL PERCENT_RANK with Partition By Example
How to return the percentage ranks for the partitioned records?. The following PERCENT_RANK query will partition the data by Occupation using their Sales amount, and then written the percent ranks for each partition independently.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENT_RANK() OVER ( PARTITION BY [Occupation] ORDER BY [Sales] ASC ) AS PercentRank FROM [Employee]
OUTPUT
Just to make sure you understand well. We are changing the SQL Server Group value from Occupation to Education.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,PERCENT_RANK() OVER ( PARTITION BY [Education] ORDER BY [Sales] ASC ) AS PercentRank FROM [Employee]
OUTPUT