> This page location: Window Functions > DENSE_RANK
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL DENSE_RANK Function

**Summary**: in this tutorial, you are going to learn how to use the PostgreSQL `DENSE_RANK()` function to assign a rank to each row within a partition of a result set, with no gaps in ranking values.

## Introduction to PostgreSQL DENSE_RANK() function

The `DENSE_RANK()` assigns a rank to every row in each partition of a result set. Different from the `RANK()` function, the `DENSE_RANK()` function always returns consecutive rank values.

For each partition, the `DENSE_RANK()` function returns the same rank for the rows which have the same values

The following shows the syntax of the `DENSE_RANK()` function:

```sql
DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```

The `DENSE_RANK()` function is applied to every row in each partition defined by the `PARTITION BY` clause, in the sort order specified by `ORDER BY` clause. It will reset the rank when crossing the partition boundary.

The `PARITION BY` clause is optional. If you skip it, the `DENSE_RANK()` function will treat the whole result set as a single partition.

## PostgreSQL DENSE_RANK() function demo

First, [create a table](../postgresql-tutorial/postgresql-create-table) named `dense_ranks` that has one column:

```sql
CREATE TABLE dense_ranks (
	c VARCHAR(10)
);
```

Second, [insert some rows](../postgresql-tutorial/postgresql-insert) into the `dense_ranks` table:

```sql
INSERT INTO dense_ranks(c)
VALUES('A'),('A'),('B'),('C'),('C'),('D'),('E');
```

Third, [query data](../postgresql-tutorial/postgresql-select) from the `dense_ranks` table:

```sql
SELECT c from dense_ranks;
```

![](https://neon.com/postgresqltutorial/PostgreSQL-DENSE_RANK-Function-Sample-Table.png)
Fourth, use the `DENSE_RANK()` function to assign a rank to each row in the result set:

```sql
SELECT
	c,
	DENSE_RANK() OVER (
		ORDER BY c
	) dense_rank_number
FROM
	dense_ranks;
```

Here is the output:

![PostgreSQL DENSE\_RANK Function example](https://neon.com/postgresqltutorial/PostgreSQL-DENSE_RANK-Function-example.png)

## PostgreSQL DENSE_RANK() function examples

We will use the `products` table to demonstrate the `DENSE_RANK()` function.

![](https://neon.com/postgresqltutorial/products_product_groups_tables.png)

![](https://neon.com/postgresqltutorial/products-table-sample-data.png)

### 1) Using PostgreSQL DENSE_RANK() function over a result set example

This statement uses the `DENSE_RANK()` function to rank products by list prices:

```sql
SELECT
	product_id,
	product_name,
	price,
	DENSE_RANK () OVER (
		ORDER BY price DESC
	) price_rank
FROM
	products;
```

Here is the output:

![](https://neon.com/postgresqltutorial/PostgreSQL-DENSE_RANK-Function-over-a-result-set.png)
In this example, we skipped the `PARTITION BY` clause, therefore, the `DENSE_RANK()` function treated the whole result set as a single partition.

The `DENSE_RANK()` function assigned a rank to each product based on the price order from high to low specified by the `ORDER BY` clause.

### 2) Using PostgreSQL DENSE_RANK() function over partitions example

The following example assigns a rank to every product in each product group:

```sql
SELECT
	product_id,
	product_name,
	group_id,
	price,
	DENSE_RANK () OVER (
		PARTITION BY group_id
		ORDER BY price DESC
	) price_rank
FROM
	products;
```

This picture shows the output:

![](https://neon.com/postgresqltutorial/PostgreSQL-DENSE_RANK-Function-over-a-partition.png)
In this example, the `PARTITION BY` clause distributed the products into product groups. The `ORDER BY` clause sorted products in each group by their prices from high to low to which the `DENSE_RANK()` function is applied.

### 3) Using PostgreSQL DENSE_RANK() function with a CTE example

The following statement uses the `DENSE_RANK()` function with a CTE to return the most expensive product in each product group:

```sql
WITH cte AS(
	SELECT
		product_id,
		product_name,
		group_id,
		price,
		DENSE_RANK () OVER (
			PARTITION BY group_id
			ORDER BY price DESC
		) price_rank
	FROM
		products
)
SELECT
	product_id,
	product_name,
	price
FROM
	cte
WHERE
	price_rank = 1;
```

![PostgreSQL DENSE\_RANK Function top rows example](https://neon.com/postgresqltutorial/PostgreSQL-DENSE_RANK-Function-top-rows-example.png)
In this tutorial, you have learned how to use the PostgreSQL `DENSE_RANK()` function to calculate a rank to each row within a partition of a result set, with no gaps in rank values.

---

## Related docs (Window Functions)

- [CUME_DIST](https://neon.com/postgresql/postgresql-window-function/postgresql-cume_dist-function)
- [FIRST_VALUE](https://neon.com/postgresql/postgresql-window-function/postgresql-first_value-function)
- [LAG](https://neon.com/postgresql/postgresql-window-function/postgresql-lag-function)
- [LAST_VALUE](https://neon.com/postgresql/postgresql-window-function/postgresql-last_value-function)
- [LEAD](https://neon.com/postgresql/postgresql-window-function/postgresql-lead-function)
- [NTH_VALUE](https://neon.com/postgresql/postgresql-window-function/postgresql-nth_value-function)
- [NTILE](https://neon.com/postgresql/postgresql-window-function/postgresql-ntile-function)
- [PERCENT_RANK](https://neon.com/postgresql/postgresql-window-function/postgresql-percent_rank-function)
- [RANK](https://neon.com/postgresql/postgresql-window-function/postgresql-rank-function)
- [ROW_NUMBER](https://neon.com/postgresql/postgresql-window-function/postgresql-row_number)