Tutorial Gateway

SQL PERCENT_RANK Function

by

The SQL PERCENT_RANK is one of the Analytic Function in SQL Server, 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.

This article will show you, How to use the analytic function called PERCENT_RANK in SQL Server with an example. Before we get into the example, let us see the syntax behind this:

SQL PERCENT_RANK Function Syntax

The basic syntax of the PERCENT_RANK in SQL Server is as shown below:

SELECT PERCENT_RANK() 
               OVER (
                      PARTITION_BY_Clause 
                      ORDER_BY_Clause
                     )
FROM [Source]
  • Order_By_Clause: This clause is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.
  • Partition_By_Clause: It divides the records selected by the SQL SELECT Statement into partitions.
    • If you specified the Partition By Clause, then PERCENT_RANK Function will start ranking for the rows in each partition.
    • If you haven’t specified the Partition By Clause, then PERCENT_RANK Function will consider all rows as a single partition.

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

SQL PERCENT_RANK 0

SQL PERCENT_RANK without Partition By Clause

In this example, we will show you, What will happen if we omit the Partition By Clause in the PERCENT_RANK Function.

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENT_RANK() OVER (ORDER BY [Sales] ASC) AS PercentRank 
  FROM [Employee]

OUTPUT

SQL PERCENT_RANK 1

ANALYSIS

First, Below statement will sort the Employee table based on their Sales Amount in an Ascending order

ORDER BY [Sales] ASC

Next, the PERCENT_RANK function will return 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 will rank the least value as 1, and the highest value as 0.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENT_RANK() OVER (ORDER BY [Sales] DESC) AS PercentRank 
  FROM [Employee]

OUTPUT

SQL PERCENT_RANK 2

SQL PERCENT_RANK with Partition By Example

In this example, we will show you, How to return the percentage ranks for the partitioned records present in a table.

The following SQL Query will partition the data by Occupation using their Sales amount, and then written the percent ranks for each partition independently.

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENT_RANK() OVER (
			     PARTITION BY [Occupation]
			     ORDER BY [Sales] ASC
			    ) AS PercentRank 
  FROM [Employee]

OUTPUT

SQL PERCENT_RANK 3

ANALYSIS

Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

Below statement will sort the partitioned data in the Ascending order using their Sales Amount

ORDER BY [Sales] ASC

In the below statement we used PERCENT_RANK Function with Partition by clause. It will rank independently in each individual partition.

PERCENT_RANK() OVER (
		      PARTITION BY [Occupation]
		      ORDER BY [Sales] ASC
		    ) AS PercentRank

Just to make sure you understand well. We are changing the Group value from Occupation to Education.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENT_RANK() OVER (
			     PARTITION BY [Education]
			     ORDER BY [Sales] ASC
			    ) AS PercentRank 
  FROM [Employee]

OUTPUT

SQL PERCENT_RANK 4

Hope you understand

Thank You for Visiting Our Blog

Placed Under: SQL