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 an example.

Row_number in PostgreSQL

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

The Row_number() function helps generate ranking, partition the result set, paginate, etc. Let’s understand it with an example.

• Suppose you have a list of products and want to know which makes the most money. The Row_number() function can rank each product based on its money-making potential. Then, based on the ranking, you can see which product makes more money and which makes less.
• Maybe you have a list of items, and you can’t see all the items at once 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.

Syntax

The syntax is given below.

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

• ROW_NUMBER(): This function assigns an integer to each row.
• OVER: This is used to specify that Row_number() will operate on the set of records or rows.
• PARTITION BY column_names: This clause specifies how you want to group the rows based on the columns, but it is optional.
• ORDER BY column_names: This option specifies how to order the rows within the group based on the selected columns.

Let’s look at a different example of practically implementing the Row_number() function.

Example 1: Simple Row Numbering

Let’s start with a simple example. Suppose we have a newemployees table and we want to assign a unique row number to each employee:

The below query will return the newemployees ordered by hire_date, with a sequential number assigned to each row.

SELECT 
    ROW_NUMBER() OVER (ORDER BY hire_date) as row_num,
    employee_id,
    first_name,
    last_name,
	hire_date
FROM newemployees;

After executing the above query, I got the expected output as shown in the screenshot below.

Example 2: For Ranking Rows

Let’s take a simple example: You are a backend developer at a product-based company, and you have been tasked with ranking the product based on the money it made 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;

After executing the above query, I got the expected output as shown in the screenshot below.

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

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

The next partition or group of sale dates is 2023-09-02, and the products Laptop and Phone are assigned ranks of 1 and 2.

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

Example 3: For Pagination

Suppose you are building an E-commerce website store and have a table of products containing 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 five products per page. This query lists the products from ID 5 to 10 on the 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;

After executing the above query, I got the expected output as shown in the screenshot below.

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 using the BETWEEN operator, as shown in the above picture. The result of the above query shows the five products from the product_id 5 to 10.

Changing the value from row_num BETWEEN 5 AND 10 to row_num BETWEEN 10 AND 15 will show the products 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 a rank to each row based on a partition or group. You have also learnt how to use row_number() functions in real life, such as ranking the product and pagination, where you learned how to show a limited number of results per page.

You may also read:

• PostgreSQL Subquery with Examples
• How to Use the Postgres Rank Function
• PostgreSQL generate_series with examples
• Postgres Min Function