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

function to calculate the rank of a row in an ordered set of rows.

## Introduction to Oracle `DENSE_RANK()`

function

The `DENSE_RANK()`

is an analytic function that calculates the rank of a row in an ordered set of rows. The returned rank is an integer starting from 1.

Unlike the `RANK()`

function, the `DENSE_RANK()`

function returns rank values as consecutive integers. It does not skip rank in case of ties. Rows with the same values for the rank criteria will receive the same rank values.

The following shows the syntax of `DENSE_RANK()`

:

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

In this syntax, the `order_by_clause`

is required because the `DENSE_RANK()`

function is ordered sensitive. The following is the syntax of the order by clause:

`ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]`

If you omit the `query_partition_by`

clause, the function will treat the whole result set as a single partition. Otherwise, the partition by clause will divide the result set into partitions to which the function applies.

`PARTITION BY expression1 [,expression2, ...]`

Note that the partition by clause must appear before the order by clause.

You will find the `DENSE_RANK()`

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

## Oracle `DENSE_RANK()`

function examples

Let’s take a simple example to understand the `DENSE_RANK()`

function:

### Oracle `DENSE_RANK()`

function illustration

First, create a new table named `dense_rank_demo`

for demonstration:

```
CREATE TABLE dense_rank_demo (
col VARCHAR2(10) NOT NULL
);
```

Next, insert some values into the `dense_rank_demo`

table:

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

Then, query data from the `dense_rank_demo`

table:

```
SELECT col FROM dense_rank_demo;
```

After that, use the `DENSE_RANK()`

function to calculate a rank for each row:

```
SELECT
col,
DENSE_RANK () OVER (
ORDER BY col )
col
FROM
dense_rank_demo;
```

The following picture shows the output:

As clearly shown in the output:

- Rows with the same values such as first and second receive the same rank values.
- Rank values are consecutive even in the event of ties.

## Oracle `DENSE_RANK()`

function examples

We’ll use the `products`

table from the sample database to demonstrate the `DENSE_RANK()`

function:

The following example uses the `DENSE_RANK()`

function to calculate rank values with the list price as a rank criterion for each product:

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

Here is the partial output:

To get the top-10 cheapest product, you use a common table expression that wraps the above query and selects only 10 products with the lowest prices as follows:

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

Here is the output:

## Oracle `DENSE_RANK()`

function with `PARTITION BY`

clause example

The following query returns the top-5 cheapest products in each category:

```
WITH cte_products AS(
SELECT
product_name,
category_id,
list_price,
RANK() OVER (
PARTITION BY category_id
ORDER BY list_price
) my_rank
FROM
products
)
SELECT * FROM cte_products
WHERE my_rank <= 5;
```

The following is the output:

In this example:

- First, the
`PARTITION BY`

clause distributed the rows from the products table into partitions by category id. - Then, the
`ORDER BY`

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

function applied to the rows in each partition. It re-initialized the rank values for each new partition.

In this tutorial, you have learned how to calculate ranks without gaps for a value in an ordered set of values by using the Oracle `DENSE_RANK()`

function.