PostgreSQL RANK Window Function

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

Getting Started with the PostgreSQL RANK Window Function #

The RANK() function assigns a rank value to each row within a partition of a result set based on the values of one or more columns.

The RANK() function reliably assigns the same rank values if the rows have the same values, ensuring predictability in your results.

Since some rows will receive the same rank values, the subsequent rows may have gaps in the ranking sequence.

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

RANK() OVER (
    [PARTITION BY partition_expresion]
    [ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• PARTITION BY divides the result set into partitions where the RANK() function applies. The partition_expression can be one or more columns you want to use to divide the result set.
• ORDER BY determines the order of rows within each partition based on the sort_expression. The RANK() function uses the columns listed in the sort_expresion of the ORDER BY clause to assign the rank values.

Both PARTITION BY and ORDER BY clauses are optional. If you omit these clauses, the RANK() function will treat all the rows as peers and assign them the same rank (1).

Therefore, the RANK() function works properly only when you have at least the ORDER BY clause. The RANK() function needs the order of the rows before applying the ranking.

PostgreSQL RANK function examples #

Let’s explore examples of using the RANK() function with the products table:

Ranking Products by Safety Stock #

The following statement uses the RANK() window function to rank the products by their safety stocks in descending order:

SELECT
    product_name,
    safety_stock,
    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 |                 8
...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 in descending order.
• The RANK() function assigns a rank value to each row.

Ranking Products Within Each Category by Safety Stock #

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

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

Try it

Output:

 category_id |        product_name        | safety_stock | rank
-------------+----------------------------+--------------+------
           3 | Apple iPhone 15 Pro Max    |           50 |    1
           3 | Huawei Mate 60             |           30 |    2
           3 | Apple iPhone 15            |           20 |    3
           3 | Samsung Galaxy Z Fold 5    |           20 |    3
           3 | Samsung Galaxy S24         |           10 |    5
           3 | Sony Xperia 1 VI           |           10 |    5
           3 | Xiaomi Mi 14               |            0 |    7
           4 | Samsung Galaxy Tab S9      |           60 |    1
           4 | Apple iPad Pro 12.9        |           15 |    2
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this query:

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

To retrieve the category name, you can join the products table with the categories table:

SELECT
  category_name,
  product_name,
  safety_stock,
  RANK() OVER (
    PARTITION BY category_name
    ORDER BY safety_stock DESC
  ) AS rank
FROM
  products p
  JOIN categories USING (category_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

category_name |        product_name        | safety_stock | rank
---------------+----------------------------+--------------+------
 Accessories   | Apple AirPods Pro 3        |           45 |    1
 Accessories   | Samsung Galaxy Buds Pro 2  |           30 |    2
 Audio Systems | Bose SoundLink Max         |            0 |    1
 Audio Systems | Sony HT-A7000 Soundbar     |            0 |    1
 Desktops      | Apple iMac 24"             |            0 |    1
 Desktops      | Dell Inspiron 27           |            0 |    1
 Laptops       | Microsoft Surface Laptop 5 |            0 |    1
 Laptops       | Dell XPS 15                |            0 |    1
 Laptops       | Lenovo ThinkPad X1 Carbon  |            0 |    1
 Laptops       | HP Spectre x360            |            0 |    1
 Smartphones   | Apple iPhone 15 Pro Max    |           50 |    1
 Smartphones   | Huawei Mate 60             |           30 |    2
 Smartphones   | Apple iPhone 15            |           20 |    3
...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 RANK() function to assign a rank value to each row within a partition of a result set.
• The RANK() function assigns the same rank values to the rows with the same values.
• The RANK() function may create gaps for subsequent rows.

Quiz #