All Products
Search
Document Center

PERCENT_RANK

Last Updated: Jun 18, 2021

The PERCENT_RANK function is similar to the CUME_DIST (cumulative distribution) function. The return value ranges from 0 to 1. The PERCENT_RANK function of the first row in a set is 0. The return value is NUMBER.

Syntax

PERCENT_RANK( ) OVER ([query_partition_clause] order_by_clause)

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

Return type

The numeric data type is returned.

Examples

Categorize students to four levels based on scores to determine the awards for the students. To create the course_rank table and insert data into the table, execute the following statements:

CREATE TABLE course_rank
(
  name VARCHAR(8),
    id NUMBER
);
INSERT INTO course_rank VALUES('Linda',1);
INSERT INTO course_rank VALUES('Tan',2);
INSERT INTO course_rank VALUES('Tom',3);
INSERT INTO course_rank VALUES('John',4);
INSERT INTO course_rank VALUES('Mery',5);
COMMIT;

Execute the following statement:

SELECT name, id ,percent_rank() OVER (ORDER BY id) AS pr1 FROM course_rank;

The following query result is returned:

+-------+------+------+
| NAME  | ID   | PR1  |
+-------+------+------+
| Linda |    1 |    0 |
| Tan   |    2 |  .25 |
| Tom   |    3 |   .5 |
| John  |    4 |  .75 |
| Mery  |    5 |    1 |
+-------+------+------+