PERCENT_RANK

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

PERCENT_RANK is an analytic function, and is similar to the CUME_DIST (cumulative distribution) function. For a row R, PERCENT_RANK calculates the rank of R minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition). The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.

Example

The following example calculates, for each employee, the percent rank of the employee's salary within the department:

SELECT deptno, ename, sal, 
   PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr
   FROM emp;

    DEPTNO ENAME             SAL         PR
---------- ---------- ---------- ----------
        10 KING             5000          0
        10 CLARK            2450         .5
        10 MILLER           1300          1
        20 SCOTT            3000          0
        20 FORD             3000          0
        20 JONES            2975         .5
        20 ADAMS            1100        .75
        20 SMITH             800          1
        30 BLAKE            2850          0
        30 ALLEN            1600         .2
        30 TURNER           1500         .4
        30 WARD             1250         .6
        30 MARTIN           1250         .6
        30 JAMES             950          1