Definition:
The Oracle PL/SQL PERCENT_RANK function is used to get the percentile of a record with respect to all records contained by the table. It returns a numeric value in range of 0 to 1. The first row in any of the group set is 0. It can be used for aggregate as well as analytic purposes.
The computation formula used by PERCENT_RANK is (x-1) / (y-1), where "x" is the rank of a row in a group which contains "y" rows.
Example Syntax:
Aggregate Usage:
PERCENT_RANK (EXPRESSION) WITHIN GROUP (ORDER BY [EXPRESSION] [ASC | DESC] [NULLS FIRST | NULLS LAST] )
Analytic Usage:
PERCENT_RANK() OVER ( PARTITION BY [EXPRESSION] ORDER BY [EXPRESSION] )
Example Usage:
SELECT EMPNO, DEPTNO, PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER_BY SALARY) "% IN DEPT"
FROM EMPLOYEE
Related Links:
Related Code Snippets:
- PERCENT_RANK - For a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than ...
