**Summary**: in this tutorial, you will learn how to use Oracle `RANK()`

function to calculate the rank of rows within a set of rows.

## Introduction to Oracle `RANK()`

function

The `RANK()`

function is an analytic function that calculates the rank of a value in a set of values.

The `RANK()`

function returns the same rank for the rows with the same values. It adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

The `RANK()`

function is useful for top-N and bottom-N queries.

The following illustrates the syntax of the `RANK()`

function:

`RANK() OVER ([ query_partition_clause ] order_by_clause)`

The `order_by_clause`

is required. It species the order of rows in each partition to which the `RANK()`

function applies.

The query partition clause, if available, divides the rows into partitions to which the `RANK()`

function applies. In case the query partition cause is omitted, the whole result set is treated as a single partition.

## Oracle `RANK()`

function examples

First, create a new table named `rank_demo`

that consists of one column:

```
CREATE TABLE rank_demo (
col VARCHAR(10) NOT NULL
);
```

Second, insert some rows into the `rank_demo`

table:

```
INSERT ALL
INTO rank_demo(col) VALUES('A')
INTO rank_demo(col) VALUES('A')
INTO rank_demo(col) VALUES('B')
INTO rank_demo(col) VALUES('C')
INTO rank_demo(col) VALUES('C')
INTO rank_demo(col) VALUES('C')
INTO rank_demo(col) VALUES('D')
SELECT 1 FROM dual;
```

Third, query from the `rank_demo`

table:

```
SELECT col FROM rank_demo;
```

Fourth, use the `RANK()`

function to calculate the rank for each row of the `rank_demo`

table:

```
SELECT
col,
RANK() OVER (ORDER BY col) my_rank
FROM
rank_demo;
```

The following shows the output:

The first two rows received the same rank 1. The third row got the rank 3 because the second row already received the rank 1. The next three rows received the same rank 4 and the last row got the rank 7.

## Oracle `RANK()`

function examples

We’ll use the `products`

table from the sample database for demonstration.

### Oracle `RANK()`

function simple example

The following statement calculates the rank of each product by its list price:

```
SELECT
product_name,
list_price,
RANK() OVER(ORDER BY list_price DESC)
FROM
products;
```

Here is the partial output:

To get the top 10 most expensive products, you use the following statement:

```
WITH cte_products AS (
SELECT
product_name,
list_price,
RANK() OVER(ORDER BY list_price DESC) price_rank
FROM
products
)
SELECT
product_name,
list_price,
price_rank
FROM
cte_products
WHERE
price_rank <= 10;
```

In this example, the common table expression returned products with their ranks and the outer query selected only the first 10 most expensive products.

Here is the output:

### Using Oracle `RANK()`

function with `PARTITION BY`

example

The following example returns the top-3 most expensive products for each category:

```
WITH cte_products AS (
SELECT
product_name,
list_price,
category_id,
RANK() OVER(
PARTITION BY category_id
ORDER BY list_price DESC)
price_rank
FROM
products
)
SELECT
product_name,
list_price,
category_id,
price_rank
FROM
cte_products
WHERE
price_rank <= 3;
```

In this example:

- First, the
`PARTITION BY`

clause divided the products into multiple partitions by category. - Then, the
`ORDER BY`

clause sorted the rows in each partition by list price in descending order. - Finally, the
`RANK()`

function calculated the rank for each row in each partition. It re-initialized the rank for each partition.

The following shows the output:

In this tutorial, you have learned how to calculate the rank of a value in a set of values by using the Oracle `RANK()`

function.