PostgreSQL DENSE_RANK Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL DENSE_RANK() function to assign a rank value, without gaps, to each row within a partition of a result set,

Getting Started with the PostgreSQL DENSE_RANK window function #

The DENSE_RANK() function assigns a rank value to each row within a partition based on the values of one or more columns. It reliably assigns the same rank values if the rows have the same values.

Unlike the RANK() function, which creates gaps in the ranking sequence, the DENSE_RANK() function does not.

Here’s the syntax of the DENSE_RANK() function:

DENSE_RANK() OVER (
    [PARTITION BY expression_list]
    [ORDER BY expression_list]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• PARTITION BY: Divides the result set into partitions where the DENSE_RANK() function applies.
• ORDER BY: Sorts rows within each partition. The values of the columns listed in the DENSE_RANK() function will determine rank values.

PostgreSQL DENSE_RANK Function Examples #

Let’s take some examples of using the DENSE_RANK() function with the products table:

Ranking Products by Safety Stock #

The following statement uses the DENSE_RANK() window function to rank the products by their safety stocks from high to low:

SELECT
    product_name,
    safety_stock,
    DENSE_RANK() OVER (
        ORDER BY safety_stock DESC
    ) AS safety_stock_rank
FROM
    products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | safety_stock | safety_stock_rank
----------------------------+--------------+-------------------
 Samsung Galaxy Tab S9      |           60 |                 1
 Apple iPhone 15 Pro Max    |           50 |                 2
 Apple AirPods Pro 3        |           45 |                 3
 Samsung QN900C Neo QLED    |           40 |                 4
 Sony Bravia XR A95K        |           30 |                 5
 Huawei Mate 60             |           30 |                 5
 Samsung Galaxy Buds Pro 2  |           30 |                 5
 LG OLED TV C3              |           20 |                 6
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The ORDER BY safety_stock DESC clause orders the rows by safety stock from high to low.
• The DENSE_RANK() function assigns a rank value to each row.

Ranking Products Within Each Brand by Safety Stock #

The following example ranks products within each brand by their safety stock in descending order.

SELECT
  brand_id,
  product_name,
  safety_stock,
  DENSE_RANK() OVER (
    PARTITION BY
      brand_id
    ORDER BY
      safety_stock DESC
  ) AS safety_stock_rank
FROM
  products p;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id |        product_name        | safety_stock | safety_stock_rank
----------+----------------------------+--------------+-------------------
        1 | Samsung Galaxy Tab S9      |           60 |                 1
        1 | Samsung QN900C Neo QLED    |           40 |                 2
        1 | Samsung Galaxy Buds Pro 2  |           30 |                 3
        1 | Samsung Galaxy Z Fold 5    |           20 |                 4
        1 | Samsung Galaxy Watch 6     |           15 |                 5
        1 | Samsung Galaxy S24         |           10 |                 6
        2 | Apple iPhone 15 Pro Max    |           50 |                 1
        2 | Apple AirPods Pro 3        |           45 |                 2
        2 | Apple iPhone 15            |           20 |                 3
        2 | Apple Watch Series 9       |           20 |                 3
        2 | Apple iPad Pro 12.9        |           15 |                 4
        2 | Apple iMac 24"             |            0 |                 5
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this query:

• The PARTITION BY brand_id clause divides the result set into partitions by brand_id.
• The ORDER BY safety_stock DESC clause orders the rows within each partition by safety stock in descending order.

To retrieve the brand name, you can join the products table with the brands table:

SELECT
    brand_name,
    product_name,
    safety_stock,
    DENSE_RANK() OVER (
        PARTITION BY brand_name
        ORDER BY safety_stock DESC
    ) AS safety_stock_rank
FROM
    products p
JOIN brands USING (brand_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name |        product_name        | safety_stock | safety_stock_rank
------------+----------------------------+--------------+-------------------
 Apple      | Apple iPhone 15 Pro Max    |           50 |                 1
 Apple      | Apple AirPods Pro 3        |           45 |                 2
 Apple      | Apple iPhone 15            |           20 |                 3
 Apple      | Apple Watch Series 9       |           20 |                 3
 Apple      | Apple iPad Pro 12.9        |           15 |                 4
 Apple      | Apple iMac 24"             |            0 |                 5
 Dell       | LG OLED TV C3              |           20 |                 1
 Dell       | Bose SoundLink Max         |            0 |                 2
 Dell       | Dell Inspiron 27           |            0 |                 2
 Dell       | Dell XPS 15                |            0 |                 2
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The PARTITION BY category_name clause divides the result set into partitions by category_name.
• The ORDER BY safety_stock DESC clause sorts the rows within each partition by safety stock in descending order.

Summary #

• Use the PostgreSQL DENSE_RANK() function to assign a rank value to each row within a partition of a result set.
• The DENSE_RANK() function assigns the same rank values to the rows with the same values and does not create gaps for subsequent rows.

Quiz #