# PERCENT_RANK

The `PERCENT_RANK()` is a window function that calculates the percentile ranking of rows in a result set.

Syntax:

``````PERCENT_RANK (expr)
OVER ([partition_by_clause] order_by_clause)
``````

Calculates the rank, expressed as a percentage, of each row within a group of rows. If `rank` is the value for that same row from the `RANK()` function (from 1 to the total number of rows in the partition group), then the `PERCENT_RANK()` value is calculated as ```(rank - 1) / (rows_in_group - 1)``` . If there is only a single item in the partition group, its `PERCENT_RANK()` value is 0.

The `ORDER BY` clause is required. The `PARTITION BY` clause is optional. The window clause is not allowed.

Usage notes:

This function is similar to the `RANK` and `CUME_DIST()` functions: it returns an ascending sequence representing the position of each row within the rows of the same partition group. The actual numeric sequence is calculated differently, and the handling of duplicate (tied) values is different.

The return values range from 0 to 1 inclusive. The first row in each partition group always has the value 0. A `NULL` value is considered the lowest possible value. In the case of duplicate input values, all the corresponding rows in the result set have an identical value: the lowest `PERCENT_RANK()` value of those tied rows. (In contrast to `CUME_DIST()`, where all tied rows have the highest `CUME_DIST()` value.)

Examples:

The following example uses the same `ANIMALS` table as the examples for `CUME_DIST()` and `NTILE()`, with a few additional rows to illustrate the results where some values are `NULL` or there is only a single row in a partition group.

``````insert into animals values ('Komodo dragon', 'Reptile', 70);
insert into animals values ('Unicorn', 'Mythical', NULL);
insert into animals values ('Fire-breathing dragon', 'Mythical', NULL);
``````

As with `CUME_DIST()`, there is an ascending sequence for each kind of animal. For example, the “Birds” and “Mammals” rows each have a `PERCENT_RANK()` sequence that ranges from 0 to 1. The “Reptile” row has a `PERCENT_RANK()` of 0 because that partition group contains only a single item. Both “Mythical” animals have a `PERCENT_RANK()` of 0 because a `NULL` is considered the lowest value within its partition group.

``````select name, kind, percent_rank() over (partition by kind order by kilos) from animals;
+-----------------------+----------+--------------------------+
| name                  | kind     | percent_rank() OVER(...) |
+-----------------------+----------+--------------------------+
| Mouse                 | Mammal   | 0                        |
| Housecat              | Mammal   | 0.2                      |
| Horse                 | Mammal   | 0.4                      |
| Polar bear            | Mammal   | 0.6                      |
| Giraffe               | Mammal   | 0.8                      |
| Elephant              | Mammal   | 1                        |
| Komodo dragon         | Reptile  | 0                        |
| Owl                   | Bird     | 0                        |
| California Condor     | Bird     | 0.25                     |
| Andean Condor         | Bird     | 0.25                     |
| Condor                | Bird     | 0.25                     |
| Ostrich               | Bird     | 1                        |
| Fire-breathing dragon | Mythical | 0                        |
| Unicorn               | Mythical | 0                        |
+-----------------------+----------+--------------------------+
``````