SQL PERCENT_RANK 3

SQL PERCENT_RANK Function

The SQL PERCENT_RANK is one of the Analytic Function, which will calculate the relative rank of each row. This 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 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 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]
SQL PERCENT_RANK Order By Descending 2

PERCENT_RANK with Partition By Example

How to return the percentage ranks for the partitioned records?. The following SQL 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]
percent RANK With Partition 3

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

Comments are closed.