Skip Headers
Oracle® Database SQL Language Reference
12c Release 1 (12.1)

E17209-15
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

PERCENT_RANK

Aggregate Syntax

percent_rank_aggregate::=

Description of percent_rank_aggregate.gif follows
Description of the illustration percent_rank_aggregate.gif

Analytic Syntax

percent_rank_analytic::=

Description of percent_rank_analytic.gif follows
Description of the illustration percent_rank_analytic.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

PERCENT_RANK is similar to the CUME_DIST (cumulative distribution) function. 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. The return value is NUMBER.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Aggregate Example

The following example calculates the percent rank of a hypothetical employee in the sample table hr.employees with a salary of $15,500 and a commission of 5%:

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
       (ORDER BY salary, commission_pct) "Percent-Rank" 
  FROM employees;

Percent-Rank
------------
  .971962617

Analytic Example

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

SELECT department_id, last_name, salary, PERCENT_RANK() 
       OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
  FROM employees
  ORDER BY pr, salary, last_name;

DEPARTMENT_ID LAST_NAME                     SALARY         PR
------------- ------------------------- ---------- ----------
           10 Whalen                          4400          0
           40 Mavris                          6500          0
              Grant                           7000          0
. . .
           80 Vishney                        10500 .181818182
           80 Zlotkey                        10500 .181818182
           30 Khoo                            3100         .2
. . .
           50 Markle                          2200 .954545455
           50 Philtanker                      2200 .954545455
           50 Olson                           2100          1
. . .