Oracle9i SQL Reference
Release 1 (9.0.1)

Part Number A90125-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Functions, 88 of 166


PERCENT_RANK

Aggregate Syntax

percent_rank_aggregate::=


Text description of functions82.gif follows
Text description of percent_rank_aggregate

Analytic Syntax

percent_rank_analytic::=


Text description of functions28a.gif follows
Text description of percent_rank_analytic

See Also:

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

Purpose

The PERCENT_RANK function 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.

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;

DEPARTMENT_ID LAST_NAME                     SALARY         PR
------------- ------------------------- ---------- ----------
           10 Whalen                          4400          0
           40 Marvis                          6500          0
.
.
.
           80 Vishney                        10500 .176470588
           50 Everett                         3900 .181818182
           30 Khoo                            3100         .2
.
.
.
           80 Johnson                         6200 .941176471
           50 Markle                          2200 .954545455
           50 Philtanker                      2200 .954545455
           50 Olson                           2100          1

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback