Introduction to PostgreSQL DENSE_RANK() function

TheDENSE_RANK() assigns a rank to every row in each partition of a result set. Different from theRANK() function, theDENSE_RANK() function always returns consecutive rank values.

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

The following shows the syntax of theDENSE_RANK() function:

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

TheDENSE_RANK() function is applied to every row in each partition defined by thePARTITION BY clause, in the sort order specified byORDER BY clause. It will reset the rank when crossing the partition boundary.

ThePARITION BY clause is optional. If you skip it, theDENSE_RANK() function will treat the whole result set as a single partition.

PostgreSQL DENSE_RANK() function demo

First,create a table nameddense_ranks that has one column:

CREATE TABLEdense_ranks(c VARCHAR(10));

Second,insert some rows into thedense_ranks table:

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

Third,query data from thedense_ranks table:

SELECT cfrom dense_ranks;

Fourth, use theDENSE_RANK() function to assign a rank to each row in the result set:

SELECTc,DENSE_RANK() OVER (ORDER BY c) dense_rank_numberFROMdense_ranks;

Here is the output:

PostgreSQL DENSE_RANK() function examples

We will use theproducts table to demonstrate theDENSE_RANK() function.

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

This statement uses theDENSE_RANK() function to rank products by list prices:

SELECTproduct_id,product_name,price,DENSE_RANK () OVER (ORDER BY price DESC) price_rankFROMproducts;

Here is the output:

In this example, we skipped thePARTITION BY clause, therefore, theDENSE_RANK() function treated the whole result set as a single partition.

TheDENSE_RANK() function assigned a rank to each product based on the price order from high to low specified by theORDER BY clause.

2) Using PostgreSQL DENSE_RANK() function over partitions example

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

SELECTproduct_id,product_name,group_id,price,DENSE_RANK () OVER (PARTITION BY group_idORDER BY price DESC) price_rankFROMproducts;

This picture shows the output:

In this example, thePARTITION BY clause distributed the products into product groups. TheORDER BY clause sorted products in each group by their prices from high to low to which theDENSE_RANK() function is applied.

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

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

WITH cte AS(SELECTproduct_id,product_name,group_id,price,DENSE_RANK () OVER (PARTITION BY group_idORDER BY price DESC) price_rankFROMproducts)SELECTproduct_id,product_name,priceFROMcteWHEREprice_rank = 1;

In this tutorial, you have learned how to use the PostgreSQLDENSE_RANK() function to calculate a rank to each row within a partition of a result set, with no gaps in rank values.

Last updated onJuly 10, 2020

On this page

• Introduction to PostgreSQL DENSE_RANK() function
• PostgreSQL DENSE_RANK() function demo
• PostgreSQL DENSE_RANK() function examples