Introduction to the PostgreSQL ROW_NUMBER() function

TheROW_NUMBER() function is a window function that assigns a sequential integer to each row in a result set.

The following illustrates the syntax of theROW_NUMBER() function:

ROW_NUMBER()OVER(    [PARTITION BY column_1, column_2,…]    [ORDER BY column_3,column_4,…])

The set of rows on which theROW_NUMBER() function operates is called a window.

ThePARTITION BY clause divides the window into smaller sets or partitions. If you specify thePARTITION BY clause, the row number for each partition starts with one and increments by one.

Because thePARTITION BY clause is optional to theROW_NUMBER() function, therefore you can omit it, andROW_NUMBER() function will treat the whole window as a partition.

TheORDER BY clause inside theOVER clause determines the order in which the numbers are assigned.

PostgreSQL ROW_NUMBER() function examples

We will use theproducts table created in the PostgreSQL window function tutorial to demonstrate the functionality of theROW_NUMBER() function.

The following shows the data in theproducts table:

See the following query.

SELECT  product_id,  product_name,  group_id,  ROW_NUMBER ()OVER (    ORDER BY      product_id  )FROM  products;

Because we did not use thePARTITION BY clause, theROW_NUMBER() function considers the whole result set as a partition.

TheORDER BY clause sorts the result set byproduct_id, therefore, theROW_NUMBER() function assigns integer values to the rows based on the  product_id order.

In the following query, we change the column in theORDER BY clause to product_name, theROW_NUMBER() function assigns the integer values to each row based on the product name order.

SELECT  product_id,  product_name,  group_id,  ROW_NUMBER ()OVER (    ORDER BY      product_name  )FROM  products;

In the following query, we use thePARTITION BY clause to divide the window into subsets based on the values in the  group_id column. In this case, theROW_NUMBER() function assigns one to the starting row of each partition and increases by one for the next row within the same partition.

TheORDER BY clause sorts the rows in each partition by the values in theproduct_name column.

SELECT  product_id,  product_name,  group_id,  ROW_NUMBER ()OVER (    PARTITION BY group_id    ORDER BY      product_name  )FROM  products;

PostgreSQL ROW_NUMBER() function and DISTINCT operator

The following query uses theROW_NUMBER() function to assign integers to thedistinct prices from theproducts table.

SELECT  DISTINCT price,  ROW_NUMBER ()OVER (    ORDER BY      price  )FROM  productsORDER BY  price;

However, the result is not expected because it includes duplicate prices. The reason is that theROW_NUMBER() operates on the result set before theDISTINCT is applied.

To solve this problem, we can get a list of distinct prices in a CTE, then apply theROW_NUMBER() function in the outer query as follows:

WITH pricesAS (  SELECT    DISTINCT price  FROM    products)SELECT  price,  ROW_NUMBER ()OVER (    ORDER BY      price  )FROM  prices;

Or we can use asubquery in theFROM clause to get a list of unique prices, and then apply theROW_NUMBER() function in the outer query.

SELECT  price,  ROW_NUMBER ()OVER (    ORDER BY      price  )FROM  (    SELECT      DISTINCT price    FROM      products  ) prices;

Using the ROW_NUMBER() function for pagination

In application development, you use the pagination technique for displaying a subset of rows instead of all rows in a table.

Besides using theLIMIT clause, you can use theROW_NUMBER() function for the pagination.

For example, the following query selects the five rows starting at row number 6:

SELECT  *FROM  (    SELECT      product_id,      product_name,      price,      ROW_NUMBER ()OVER (        ORDER BY          product_name      )as rn    FROM      products  ) xWHERE  rnBETWEEN 6 AND 10;

Using the ROW_NUMBER() function for getting the nth highest / lowest row

For example, to get the third most expensive products, first, we get the distinct prices from the products table and select the price whose row number is 3. Then, in the outer query, we get the products with the price that equals the 3rd highest price.

SELECT  *FROM  productsWHERE  price= (    SELECT      price    FROM      (        SELECT          price,          ROW_NUMBER ()OVER (            ORDER BY              priceDESC          ) nth        FROM          (            SELECT              DISTINCT (price)            FROM              products          ) prices      ) sorted_prices    WHERE      nth= 3  );

Summary

• Use the PostgreSQLROW_NUMBER() function to assign integer values to rows in a result set.

Last updated onFebruary 1, 2024

On this page

• Introduction to the PostgreSQL ROW_NUMBER() function
• PostgreSQL ROW_NUMBER() function examples
• PostgreSQL ROW_NUMBER() function and DISTINCT operator
• Using the ROW_NUMBER() function for pagination
• Using the ROW_NUMBER() function for getting the nth highest / lowest row
• Summary