# How the PERCENT_RANK() function works in Mariadb?

The `PERCENT_RANK()`

function is a built-in function in Mariadb that returns the relative rank of a row within a group of rows.

The `PERCENT_RANK()`

function is a built-in function in Mariadb that returns the relative rank of a row within a group of rows. The function is useful for calculating the percentile or the percentage of values that are less than or equal to a given value. The function is also known as `PERCENTILE_RANK()`

.

## Syntax

The syntax of the `PERCENT_RANK()`

function is as follows:

```
PERCENT_RANK() OVER (
PARTITION BY expr1, expr2, ...
ORDER BY expr3, expr4, ...
)
```

Where `expr1`

, `expr2`

, … are the expressions that define the partition or the group of rows, and `expr3`

, `expr4`

, … are the expressions that define the order or the ranking of the rows within each partition. The function returns a decimal value between 0 and 1, inclusive.

## Examples

### Example 1: Calculating the percent rank of students’ scores

The following example shows how to use the `PERCENT_RANK()`

function to calculate the percent rank of students’ scores in a table called `students`

:

```
SELECT name, score, PERCENT_RANK() OVER (ORDER BY score) AS PercentRank
FROM students;
```

The output is:

```
+-------+-------+-------------+
| name | score | PercentRank |
+-------+-------+-------------+
| Bob | 50 | 0.0000 |
| Alice | 60 | 0.2500 |
| Eve | 70 | 0.5000 |
| Dave | 80 | 0.7500 |
| Carol | 90 | 1.0000 |
+-------+-------+-------------+
```

The function returns the percent rank of each student’s score based on the ascending order of the scores. The percent rank is calculated as `(rank - 1) / (total rows - 1)`

, where rank is the ordinal rank of the row. For example, the percent rank of Bob’s score is `(1 - 1) / (5 - 1) = 0.0000`

, and the percent rank of Carol’s score is `(5 - 1) / (5 - 1) = 1.0000`

.

### Example 2: Calculating the percent rank of products’ sales

The following example shows how to use the `PERCENT_RANK()`

function to calculate the percent rank of products’ sales in a table called `products`

:

```
SELECT product, category, sales, PERCENT_RANK() OVER (PARTITION BY category ORDER BY sales) AS PercentRank
FROM products;
```

The output is:

```
+---------+----------+-------+-------------+
| product | category | sales | PercentRank |
+---------+----------+-------+-------------+
| A | Books | 100 | 0.0000 |
| B | Books | 200 | 0.3333 |
| C | Books | 300 | 0.6667 |
| D | Books | 400 | 1.0000 |
| E | Toys | 50 | 0.0000 |
| F | Toys | 100 | 0.5000 |
| G | Toys | 150 | 1.0000 |
+---------+----------+-------+-------------+
```

The function returns the percent rank of each product’s sales based on the ascending order of the sales within each category. The percent rank is calculated as `(rank - 1) / (total rows in partition - 1)`

, where rank is the ordinal rank of the row within the partition. For example, the percent rank of product A’s sales is `(1 - 1) / (4 - 1) = 0.0000`

, and the percent rank of product G’s sales is `(3 - 1) / (3 - 1) = 1.0000`

.

## Related Functions

There are some other functions in Mariadb that are related to the `PERCENT_RANK()`

function. They are:

`RANK()`

: This function returns the ordinal rank of a row within a group of rows. The function assigns the same rank to rows with the same values, and leaves gaps between ranks. The function is also known as`RANK_DENSE()`

.`DENSE_RANK()`

: This function returns the ordinal rank of a row within a group of rows. The function assigns the same rank to rows with the same values, and does not leave gaps between ranks. The function is also known as`RANK_EQUAL()`

.`ROW_NUMBER()`

: This function returns the sequential number of a row within a group of rows. The function assigns a unique number to each row, starting from 1.

## Conclusion

The `PERCENT_RANK()`

function is a useful function in Mariadb that allows you to calculate the relative rank of a row within a group of rows. The function is helpful for calculating the percentile or the percentage of values that are less than or equal to a given value. You can also use other functions like `RANK()`

, `DENSE_RANK()`

, and `ROW_NUMBER()`

to assign ranks to rows in different ways. I hope this article helped you understand how the `PERCENT_RANK()`

function works in Mariadb.