SQL PERCENT_RANK Function

The SQL Server PERCENT_RANK is one of the Analytic functions, which will calculate the relative rank of each row. This function will return the rank from a range of values greater than 0 and less than 1. The basic syntax of the SQL PERCENT_RANK is:

SELECT PERCENT_RANK() 
               OVER (
                      PARTITION_BY_Clause 
                      ORDER_BY_Clause
                     )
FROM [Source]

We are going to use the below-shown data for this demonstration

Employee Table 0

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.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENT_RANK() OVER (ORDER BY [Sales] ASC) AS PercentRank 
  FROM [Employee]
SQL PERCENT_RANK Order By Ascending 1

The Order By Clause Sort the Employee table Ascending order using their Sales Amount

ORDER BY [Sales] ASC

Next, the SQL PERCENT_RANK function returns the percentage ranks as the output. Here, a row with the highest sales will assign 1 as the rank, and 0 is the percentile rank for the least sales.

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]
SQL PERCENT_RANK Order By Descending 2

SQL PERCENT_RANK with Partition By Example

How do 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]
With Partition 3

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]
SQL PERCENT_RANK With Partition 4

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.