PostgreSQL ROW_NUMBER Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL ROW_NUMBER window function to assign unique sequential integers to rows within a partition of a result set.

Getting Started with the PostgreSQL ROW_NUMBER window function #

The ROW_NUMBER() is a window function that assigns unique sequential integers to rows within a partition of a result set.

The ROW_NUMBER() function can be helpful for queries that perform ranking, pagination, and identifying duplicate rows.

Here’s the basic syntax of the ROW_NUMBER() function:

ROW_NUMBER() 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 ROW_NUMBER() function applies.
ORDER BY: Sorts the rows within each partition.

Basic PostgreSQL ROW_NUMBER() window function examples #

Let’s explore some examples of using the ROW_NUMBER() function with the products table from the inventory database:

The following example uses the ROW_NUMBER() function to assign a unique row number to each row in the products table:

SELECT
  product_name,
  price,
  ROW_NUMBER() OVER () as row_number
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

        product_name        |  price  | row_number
----------------------------+---------+------------
 Samsung Galaxy S24         |  999.99 |          1
 Apple iPhone 15            | 1099.99 |          2
 Huawei Mate 60             |  899.99 |          3
 Xiaomi Mi 14               |  799.99 |          4
 Sony Xperia 1 VI           |  949.99 |          5
 Samsung Galaxy Z Fold 5    | 1799.99 |          6
 Apple iPhone 15 Pro Max    | 1299.99 |          7
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The FROM clause examines all rows from the products table.
The SELECT clause retrieves the product name and price and calls the ROW_NUMBER() function to generate a unique row number for each row.

If you want to control the order of rows, you need to use the ORDER BY clause in the OVER, not in the SELECT statement:

SELECT
  product_name,
  price,
  ROW_NUMBER() OVER (ORDER BY  product_name) row_number
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price  | row_number
----------------------------+---------+------------
 Apple AirPods Pro 3        |  249.99 |          1
 Apple iMac 24"             | 1299.99 |          2
 Apple iPad Pro 12.9        | 1099.99 |          3
 Apple iPhone 15            | 1099.99 |          4
 Apple iPhone 15 Pro Max    | 1299.99 |          5
 Apple Watch Series 9       |  399.99 |          6
 Bose SoundLink Max         |  399.99 |          7
 Dell Inspiron 27           |  999.99 |          8
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The FROM clause examines all rows from the products table.
The SELECT clause retrieves the product name and price and calls the ROW_NUMBER() function to generate a sequential row number for each row sorted by the product name in ascending order.

To assign row numbers to products within each category, you can use the PARTITION BY clause:

SELECT
  product_name,
  price,
  category_id,
  ROW_NUMBER() OVER (
    PARTITION BY category_id
    ORDER BY product_name
  ) row_number
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price  | category_id | row_number
----------------------------+---------+-------------+------------
 Apple iPhone 15            | 1099.99 |           3 |          1
 Apple iPhone 15 Pro Max    | 1299.99 |           3 |          2
 Huawei Mate 60             |  899.99 |           3 |          3
 Samsung Galaxy S24         |  999.99 |           3 |          4
 Samsung Galaxy Z Fold 5    | 1799.99 |           3 |          5
 Sony Xperia 1 VI           |  949.99 |           3 |          6
 Xiaomi Mi 14               |  799.99 |           3 |          7
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The PARTITION BY clause divides the rows into partitions by category id.
The ORDER BY clause sorts rows in each partition by the product name.
The ROW_NUMBER() assigns a sequential integer to products in each partition.

Notice that the ROW_NUMBER() function resets the row number for each partition.

PostgreSQL ROW_NUMBER function applications #

Let’s explore some applications of the ROW_NUMBER() function.

Pagination #

The ROW_NUMBER() function allows you to paginate data by retrieving a specific subset of rows from a large result set.

The following example uses the ROW_NUMBER() function with a common table expression (CTE) to retrieve the row numbers 11 to 20:

WITH numbered_products AS (
    SELECT
      product_id,
      product_name,
      price,
      ROW_NUMBER() OVER (
        ORDER BY product_name
      ) row_number
    FROM
      products
)
SELECT
  product_id,
  product_name,
  price
FROM
  numbered_products
WHERE
  row_number BETWEEN 11 AND 20
ORDER BY product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |        product_name        |  price
------------+----------------------------+---------
          3 | Huawei Mate 60             |  899.99
         23 | Lenovo ThinkPad X1 Carbon  | 1599.99
         17 | LG G3 OLED                 | 2499.99
         14 | LG OLED TV C3              | 1999.99
         22 | Microsoft Surface Laptop 5 | 1299.99
         11 | Samsung Galaxy Buds Pro 2  |  199.99
          1 | Samsung Galaxy S24         |  999.99
          8 | Samsung Galaxy Tab S9      |  699.99
         13 | Samsung Galaxy Watch 6     |  349.99
          6 | Samsung Galaxy Z Fold 5    | 1799.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

First, the query of the CTE assigns a sequential number to each row from the products table sorted by the product name:

SELECT
  product_id,
  product_name,
  price,
  ROW_NUMBER() OVER (
    ORDER BY
      product_name
  ) row_number
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |        product_name        |  price  | row_number
------------+----------------------------+---------+------------
         10 | Apple AirPods Pro 3        |  249.99 |          1
         24 | Apple iMac 24"             | 1299.99 |          2
          9 | Apple iPad Pro 12.9        | 1099.99 |          3
          2 | Apple iPhone 15            | 1099.99 |          4
          7 | Apple iPhone 15 Pro Max    | 1299.99 |          5
         12 | Apple Watch Series 9       |  399.99 |          6
         19 | Bose SoundLink Max         |  399.99 |          7
         25 | Dell Inspiron 27           |  999.99 |          8
         20 | Dell XPS 15                | 1499.99 |          9
         21 | HP Spectre x360            | 1399.99 |         10
          3 | Huawei Mate 60             |  899.99 |         11
         23 | Lenovo ThinkPad X1 Carbon  | 1599.99 |         12
         17 | LG G3 OLED                 | 2499.99 |         13
         14 | LG OLED TV C3              | 1999.99 |         14
         22 | Microsoft Surface Laptop 5 | 1299.99 |         15
         11 | Samsung Galaxy Buds Pro 2  |  199.99 |         16
          1 | Samsung Galaxy S24         |  999.99 |         17
          8 | Samsung Galaxy Tab S9      |  699.99 |         18
         13 | Samsung Galaxy Watch 6     |  349.99 |         19
          6 | Samsung Galaxy Z Fold 5    | 1799.99 |         20
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, the outer query retrieves the rows with row numbers from 11 to 20:

SELECT
  product_id,
  product_name,
  price
FROM
  numbered_products
WHERE
  row_number BETWEEN 11 AND 20;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Getting Top N records #

The following example uses the ROW_NUMBER() function to retrieve the top two most expensive products in each category:

WITH ranked_products AS (
    SELECT
      product_id,
      product_name,
      category_id,
      price,
      ROW_NUMBER() OVER (
        PARTITION BY
          category_id
        ORDER BY
          price DESC
      ) AS rank
    FROM
      products
  )
SELECT
  product_id,
  product_name,
  category_id,
  price
FROM
  ranked_products
WHERE
  rank <= 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |       product_name        | category_id |  price
------------+---------------------------+-------------+---------
          6 | Samsung Galaxy Z Fold 5   |           3 | 1799.99
          7 | Apple iPhone 15 Pro Max   |           3 | 1299.99
          9 | Apple iPad Pro 12.9       |           4 | 1099.99
          8 | Samsung Galaxy Tab S9     |           4 |  699.99
         10 | Apple AirPods Pro 3       |           5 |  249.99
         11 | Samsung Galaxy Buds Pro 2 |           5 |  199.99
         12 | Apple Watch Series 9      |           6 |  399.99
         13 | Samsung Galaxy Watch 6    |           6 |  349.99
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can get the category name by joining the CTE with the categories table:

PostgreSQL ROW_NUMBER Window Function - Sample Tables

WITH ranked_products AS (
    SELECT
      product_id,
      product_name,
      category_id,
      price,
      ROW_NUMBER() OVER (
        PARTITION BY
          category_id
        ORDER BY
          price DESC
      ) AS rank
    FROM
      products
  )
SELECT
  product_id,
  product_name,
  category_name,
  price
FROM
  ranked_products
  JOIN categories USING (category_id)
WHERE
  rank <= 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |       product_name        | category_name |  price
------------+---------------------------+---------------+---------
          6 | Samsung Galaxy Z Fold 5   | Smartphones   | 1799.99
          7 | Apple iPhone 15 Pro Max   | Smartphones   | 1299.99
          9 | Apple iPad Pro 12.9       | Tablets       | 1099.99
          8 | Samsung Galaxy Tab S9     | Tablets       |  699.99
         10 | Apple AirPods Pro 3       | Accessories   |  249.99
         11 | Samsung Galaxy Buds Pro 2 | Accessories   |  199.99
         12 | Apple Watch Series 9      | Wearables     |  399.99
         13 | Samsung Galaxy Watch 6    | Wearables     |  349.99
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Removing duplicate rows #

First, create a table called items with two columns id and name:

CREATE TABLE items (
   id INT, 
   name VARCHAR
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert rows into the items table:

INSERT INTO
  items (id, name)
VALUES
  (1, 'iPhone'),
  (2, 'iPhone'),
  (3, 'Galaxy'),
  (4, 'Galaxy'),
  (5, 'iMac') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |  name
----+--------
  1 | iPhone
  2 | iPhone
  3 | Galaxy
  4 | Galaxy
  5 | iMacCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The items table has duplicate values in the name columns with the id 2 and 4.

Third, use the ROW_NUMBER function to divide the rows by name into partitions and assign each row in the partition a sequential integer:

SELECT
  id,
  name,
  ROW_NUMBER() OVER (
    PARTITION BY
      name
    ORDER BY
      id
  ) AS row_number
FROM
  items
ORDER BY
  id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |  name  | row_number
----+--------+------------
  1 | iPhone |          1
  2 | iPhone |          2
  3 | Galaxy |          1
  4 | Galaxy |          2
  5 | iMac   |          1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The duplicate rows are the ones that have the row_number greater than 1.

You can delete these duplicate rows using a CTE with a DELETE statement:

WITH ranked_items AS (
    SELECT
      id,
      name,
      ROW_NUMBER() OVER (
        PARTITION BY
          name
        ORDER BY
          id
      ) AS row_number
    FROM
      items
  )
DELETE FROM items
WHERE id IN (
    SELECT
      id
    FROM
      ranked_items
    where
      row_number > 1
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, retrieve data from the items table to verify the removal:

SELECT
  id,
  name
FROM
  items;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |  name
----+--------
  1 | iPhone
  3 | Galaxy
  5 | iMacCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the PostgreSQL ROW_NUMBER() window function to assign unique sequential integers to rows within a partition of a result set.
Use the ROW_NUMBER function to rank rows, get top N records, and identify duplicate rows from a table.

Quiz #

Was this tutorial helpful ?