Oracle SQL / PLSQL PERCENT_RANK function is used to calculate the rank of a hypothetical row.

- As an aggregate function a rank is calculated as r minus 1 divided by the number of rows in the aggregate group.
- As an analytical function rank is calculated as r minus 1 divided by the number of rows in the aggregate group minus 1.
- A Percent_Rank returns a value between 0 and 1 inclusive.

The SQL PERCENT_RANK function can be used in as an Aggregate and Analytical Function.

## Oracle SQL / PLSQL PERCENT_RANK Function Syntax as an Aggregate Function is:

**SELECT PERCENT_RANK(expression1, expression2 . . , expressionN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name;**

## Oracle SQL / PLSQL PERCENT_RANK Function Syntax as an Analytical Function is:

**SELECT PERCENT_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;
**

## Example 1: Using Oracle SQL / PLSQL PERCENT_RANK Function as AGGREGATE function

Suppose we have a table named ‘employee’ as shown below:

Employee_Id |
Employee_Name |
Salary |
Department |
Commission |

101 | Emp A | 10000 | Sales | 10 |

102 | Emp B | 20000 | IT | 20 |

103 | Emp C | 28000 | IT | 20 |

104 | Emp D | 30000 | Support | |

105 | Emp E | 32000 | Sales | 10 |

106 | Emp F | 40000 | Sales | 10 |

If we write our query Oracle SQL PERCENT_RANK Function with SELECT Statement as:

SELECT PERCENT_RANK(15000) WITHIN GROUP (ORDER BY salary) FROM employee;

We will get ‘0.166666666666667’ as the PERCENT_RANK of 15000 because as per the data in the ‘employee’ table if we sort the ‘salary’ column from lowest to highest salary then 15000 will come at the 2^{nd} position in that list and the total number of rows will be 6.

Hence the percent_rank for 15000 is calculated as:

*(Position of the row-1/total rows) i.e. (2-1/6) = 1/6 = 0.166666666666667*

## Example 2: Using Oracle SQL / PLSQL PERCENT_RANK Function as ANALYTICAL function

As mentioned earlier, the syntax for the Oracle SQL PERCENT_RANK function as an Analytical Function is:

**SELECT PERCENT_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;
**

Suppose we have a table named ‘employee’ as shown below:

Employee_Id |
Employee_Name |
Salary |
Department |
Commission |

101 | Emp A | 10000 | Sales | 10 |

102 | Emp B | 20000 | IT | 20 |

103 | Emp C | 28000 | IT | 20 |

104 | Emp D | 30000 | Support | |

105 | Emp E | 32000 | Sales | 10 |

106 | Emp F | 40000 | Sales | 10 |

If we write our query Oracle SQL PERCENT_RANK Function with SELECT Statement as:

SELECT employee_name ,salary ,department ,PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) Percent_Ranking FROM employee;

We will get the following output:

Employee_Name |
Salary |
Department |
Percent_Ranking |

Emp B | 20000 | IT | 0 |

Emp C | 28000 | IT | 1 |

Emp A | 10000 | Sales | 0 |

Emp E | 32000 | Sales | 0.5 |

Emp F | 40000 | Sales | 1 |

Emp D | 30000 | Support | 0 |

Here we can see that Oracle SQL PERCENT_RANK function is being used as an analytical function.

It returns the position or rank of records in group of records partitioned by a criteria e.g. in our case we have partitioned the records by ‘department’ and then within that partition we are ranking the records relative to each other.

First row is always given a percent rank of ‘0’ by Oracle SQL PERCENT_RANK Function.

The PERCENT_RANK is this case is calculated as:

(Position Of the rows-1/total rows -1)

e.g. for Emp E percent rank is calculated using

(Position Of the rows-1/total rows -1) as (2-1/3-1) = (1/2) = 0.5

And for Emp F percent rank is calculated using

(Position Of the rows-1/total rows -1) as (3-1/3-1) = 2/2 = 1

## Leave a Reply