Row_number in PostgreSQL

In this PostgreSQL tutorial, I will show you how to use row_number in PostgreSQL to assign a unique sequential integer as a rank to each row.

I will also tell you the importance of the Row_number() function with syntax and examples. Additionally, you will understand how to implement Row_number() for ranking and pagination with the example.

Row_number in PostgreSQL

The Row_number() function in PostgreSQL is the window function that assigns a unique integer to each row within the set of rows. The set of rows or records is called a window.

The Row_number() function helps in generating ranking, partitioning result set and pagination etc, let’s understand with an example.

• Suppose you have a list of products and want to know which product makes how much money, you can use the Row_number() function to assign a ranking to each product based on the money they make. Then you can see based on the ranking which product makes more money and which one makes less.
• Maybe you have a list of items and you can’t see all the items at one time because the list is very long, The Row_number() function allows you to view the items per page at a time, like some items on the first page, second page etc. That is the concept of pagination, which shows a limited number of items per page.

Now you know how the Row_number() function makes things easier.

The syntax is given below.

ROW_NUMBER() OVER (PARTITION BY column_names ORDER BY column_names)

• ROW_NUMBER(): This is the function to assign an integer to each row.
• OVER: This is used to specify that Row_number() is going to operate on the set of records or rows.
• PARTITION BY column_names: This is used to specify how you want to group the rows based on the columns but this clause is optional.
• ORDER BY column_names: This is used to specify how you want to order the rows within the group based on the specified columns.

Let’s see with a different example of how you can implement the Row_number() function practically.

Ranking Rows Using Row_number in PostgreSQL

Let’s take a simple example where you are a backend developer at a product-based company and you have been given the task of ranking the product based on the money made by the product on each sale date. The database has a sales table which is shown below.

Run the below query to rank the product based on the revenue by sale date.

SELECT product_name, sale_date,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY revenue DESC) AS rank
FROM
    sales;

Look at line 3 where the Row_number() function assigns the rank to each product, grouped by sale_date and ranking for each product within the group starts from 1, and ranking is assigned in descending order based on the column revenue.

Look at the group of sale date 2023-09-01 where the product Game Console and T-shirt assigned ranks of 1 and 2 based on the revenue in descending order respectively.

The next partition or group of sale date is 2023-09-02 where the product Laptop and Phone assigned rank of 1 and 2.

Here you need to see how products are grouped or partitioned using the column sale date and rank is assigned to each row within that group starting from 1 based on revenue in descending order, and the same process for the other groups.

Pagination Using Row_number in PostgreSQL

Suppose you are building an E-commerce website store and you have table products that contain information about the product like name, price and stock quantity. You have to implement functionality to show only a limited number of products on each page in a user-friendly manner.

This is where pagination is very important, the products table is shown below.

Use the below query to display the 5 products per page and using this query list the product from id 5 to 10 on page.

SELECT
 product_id,
 product_name,
    price,
    stock_quantity
FROM (
    SELECT
 product_id,
 product_name,
        price,
        stock_quantity,
        ROW_NUMBER() OVER (ORDER BY product_id) AS row_num
    FROM
        products
) AS ranked_products
WHERE
    row_num BETWEEN 5 AND 10;

In the above query at line 12 in the Inner Query, assign a rank to each product in ascending order based on the column product_id and return the new column named row_num containing the ranking for each product.

Then in the Outer Query, limit the result in the WHERE clause by using the BETWEEN as you can see in the above picture. The result of the above query shows the 5 products from the product_id 5 to 10.

if you change the value from row_num BETWEEN 5 AND 10 to row_num BETWEEN 10 AND 15, it will show the 5 next product from the product_id 10 to 15.

Conclusion

In this PostgreSQL tutorial, you have learned how to use the row_number in PostgreSQL to assign rank to each row based on partition or group. Also, you learnt how the use of row_number() functions in real life such as for ranking the product and pagination where you learned how to show the limited number of results per page.

You may also read:

• PostgreSQL Subquery with Examples
• How to use Postgres Rank Function
• Postgresql generate_series with examples