PERCENT_RANK

The PERCENT_RANK() function is a built-in SQL function that calculates the relative rank of a row within a result set. Specifically, it calculates the percentage rank of a row based on its value compared to the other rows in the result set.

The PERCENT_RANK() function takes into account the total number of rows in the result set and calculates the relative position of a row within that set. The function returns a decimal value between 0 and 1, where 0 represents the lowest rank and 1 represents the highest rank.

Syntax

The syntax for the PERCENT_RANK() function is as follows:

PERCENT_RANK() OVER (ORDER BY column1 [, column2, ...])

The ORDER BY clause specifies the columns used to determine the order of the rows in the result set. The PERCENT_RANK() function is then applied to the ordered result set.

Example

Here’s an example query that uses the PERCENT_RANK() function:

SELECT 
employee_id, salary, 
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_rank
FROM employees;

This query selects the employee_id and salary columns from the employees table and calculates the percentage rank of each row based on the salary column. The result set includes the original columns as well as the calculated percent_rank column.

The SQL PERCENT_RANK() function is useful for analyzing the distribution of data within a result set. It can help identify outliers and provide insight into the overall distribution of data.