Postgresql row_number + Examples

In this Postgresql tutorial, we are going to learn about “Postgresql row_number” which is the row_number( ) function that assigns a unique integer to each row in queried result set.

• Postgresql row_number partition by
• Postgresql row_number vs rank
• Postgresql row_number in where clause
• Postgresql row_number without over
• Postgresql row_number group by
• Postgresql row_number without order
• Postgresql row_number start at 0
• Postgresql row_number over partition by
• Postgresql row_number over distinct
• Postgresql row_number order by partition

Postgresql row_number

The Row_number( ) function is used to assign a sequential integer to each row in the result set.

Syntax:

ROW_NUMBER() OVER(
    [PARTITION BY column_name_1, column_name_2,…]
    [ORDER BY column_name_3,column_name_4,…]
)

• The ROW_NUMBER() function operates on a set of rows and the set of rows is called a window.
• The PARTITION BY clause divides the window into partitions. Generally, when we use the PARTITION BY clause, the row number for each partition starts with a value one and increments by one as well.
• Because the PARTITION BY clause is optional, so you can omit it,
• The order of number that is assigned can be determined by using the ORDER BY clause inside the Over clause. Let’s understand it by example.

First, create two tables named items and item_groups.

CREATE TABLE item_groups (
	item_group_id serial PRIMARY KEY,
	item_group_name VARCHAR (255) NOT NULL
);

CREATE TABLE items (
	item_id serial PRIMARY KEY,
	item_name VARCHAR (255) NOT NULL,
	item_price DECIMAL (11, 2),
	item_group_id INT NOT NULL,
	FOREIGN KEY (item_group_id) REFERENCES item_groups (item_group_id)
);

Insert the following records.

INSERT INTO item_groups (item_group_name)
VALUES
	('Smartphone'),
	('Laptop'),
	('Tablet');

INSERT INTO items (item_name, item_group_id,item_price)
VALUES
	('Nokia', 1, 200),
	('HTC', 1, 400),
	('Nexus', 1, 500),
	('iPhone', 1, 900),
	('HP', 2, 1200),
	('Lenovo', 2, 700),
	('Sony VAIO', 2, 700),
	('Dell Vostro', 2, 800),
	('iPad', 3, 700),
	('Kindle Fire', 3, 150),
	('Samsung Galaxy Tab', 3, 200);

Now run the following query.

SELECT
	item_id,
	item_name,
	item_group_id,
	ROW_NUMBER () OVER (ORDER BY item_id)
FROM
	items;

The ROW_NUMBER() function considers the result set as a partition. The ORDER BY clause sorts the result set using item_id, as a result, the ROW_NUMBER() function gives integer values to each row based on the item_id order.

Read: Postgresql auto increment

Postgresql row_number partition by and without order

In Postgresql, we use the PARTITION BY clause to divide the window into smaller subsets based on the values in the item_id column, use the below query.

SELECT
	item_id,
	item_name,
	item_group_id,
	ROW_NUMBER () OVER (PARTITION BY item_id)
FROM
	items;

In this case, the ROW_NUMBER() function sets one to the starting row of each smaller set/partition, and increments by one for the next row in the same partition.

Read: PostgreSQL CREATE INDEX

Postgresql row_number vs rank

In Postgresql, Row_number( ) function assigns a unique integer to each row sequentially or returns the row number for each group on the basis of Partition By.

Let’s understand it by example.

SELECT
	item_groups.item_group_name,
	item_name,
	ROW_NUMBER () OVER (PARTITION BY items.item_group_id)
FROM items
   JOIN item_groups ON item_groups.item_group_id=items.item_group_id;

We have joined the two tables item_groups and items on item_group_id, then Partition By items.item_group_id.

It has been partitioned into smaller subsets like Smartphone, Laptop, Tablet, and Row_number( ) function assigned unique integer to each row sequentially within the smaller subsets.

Rank( ) assigns the same integer value for ties like ( 1,2,2,3 ).

SELECT
	item_groups.item_group_name,
	item_name,
	RANK () OVER (PARTITION BY item_groups.item_group_name)
FROM items
   JOIN item_groups ON item_groups.item_group_id=items.item_group_id;

This time result is different from Row_number( ) function, rank provided the same value for each partitioned.

Read: Postgresql generate_series

Postgresql row_number in where clause

In Postgresql, we are going to filter the result set from Row_number( ) function Using the WHERE clause, and also going to partition it by item_id.

SELECT
	item_id,
	item_name,
	item_group_id,
	ROW_NUMBER () OVER (PARTITION BY item_id)
FROM
	items
	WHERE item_group_id =1;

We have filtered the result set using item_group_id in the WHERE clause, and it shows the item_name (Nokia, HTC, Nexus, iPhone) that belongs to item_group_id (1).

Read: Postgresql cast int

Postgresql row_number without over

In Postgresql, to use Row_number without OVER is not possible because the OVER clause is part of the Window function ( Row_number( ) ).

Let’s see the error through an example.

SELECT
	item_id,
	item_name,
	item_group_id,
	ROW_NUMBER ()
FROM
	items;

In the above output, it shows the error window function row_number requires an OVER clause, which means the OVER clause is a necessary part of the row_number function.

Read: How to find primary column name in Postgresql

Postgresql row_number group by

In Postgresql. to apply group by clause on sets of rows, the following example outputs the average price for every item group.

SELECT
	item_groups.item_group_name,
	AVG (item_price)
FROM
	items
INNER JOIN item_groups USING (item_group_id)
GROUP BY
	item_group_name;

As we can see from the above output, we have inner joined the table item_group, and items using item_group_id column then applied aggregate function (Average function) on item_price, and grouped by item_group_name.

Read: Postgresql date_trunc function

Postgresql row_number start at 0

In Postgresql, whenever we use Row_number( ) function, it assigns numerical values from 1 to the size of the records in the table, but “what will happen” if we want to start from 0. Let’s understand it through examples.

SELECT DISTINCT
	item_name,
	item_price,
	ROW_NUMBER() OVER (ORDER BY item_price)
FROM
	items
ORDER BY
	item_price;

In the above output, look at the row_number column in red rectangle color, Row_number( ) function assigned numeric values from 1 to 10 to each record in a table, but here we want it to start from 0 to 9.

Below is a demonstration of “How to start it from 0.“

SELECT DISTINCT
	item_name,
	item_price,
	ROW_NUMBER() OVER (ORDER BY item_price) -1 as row_number
FROM
	items
ORDER BY
	item_price;

In the above output at ( ROW_NUMBER() OVER (ORDER BY item_price) -1 as row_number ), we decreased or subtracted minus one from Row_number( ) function, as a result, it assigned integer values from 0 to 9 to each record in a table named items.

Read: PostgreSQL TO_NUMBER() function

Postgresql row_number over distinct

In Postgresql, Row_number( ) function assigns numerical values to distinct item_price from table named items .

Use the below query.

SELECT DISTINCT
	item_price,
	ROW_NUMBER () OVER (ORDER BY item_price)
FROM
	items
ORDER BY
	item_price;

In the above result, it is not the expected result that we want because it includes the duplicate item_price.

The reason for that is, Row_number( ) function is applied to the result set before the DISTINCT is applied.

Let’s fix this issue by applying DISTINCT before the Row_number( ) function, we use it in a subquery of the FROM clause to get a list of unique item_price, and then apply the ROW_NUMBER( ) function in the outer query.

SELECT
	item_price,
	ROW_NUMBER () OVER (ORDER BY item_price)
FROM
	(
		SELECT DISTINCT
			item_price
		FROM
			items
	) item_prices;

Read: PostgreSQL TO_TIMESTAMP function

Postgresql row_number order by partition

In Postgresql, we can use the ORDER BY clause with the OVER clause of the Row_number( ) function, so we are going to order by the result set returned by row_number.

Let’s understand it with examples.

SELECT
	item_groups.item_group_name,
	item_name,
	item_price,
	ROW_NUMBER () OVER (PARTITION BY items.item_group_id )
FROM items
   JOIN item_groups ON item_groups.item_group_id=items.item_group_id;

As we can see in the above output, the item_price of each item_group_name column is not in any order, like item_group_name laptop has item_price 1200, 700, 700, 800.

Let’s put the item_price of each item_group_name into some order like ascending or descending.

SELECT
	item_groups.item_group_name,
	item_name,
	item_price,
	ROW_NUMBER () OVER (PARTITION BY items.item_group_id ORDER BY item_price )
FROM items
   JOIN item_groups ON item_groups.item_group_id=items.item_group_id;

Now see the order of item_price of each item_group_name is in order from low to high item_price because it is ORDER BY item_price in Over Clause of Row_number function.

You may also like to read the following articles.

• PostgreSQL TO_CHAR function
• Postgresql Rank function
• How to backup PostgreSQL database
• PostgreSQL Export Table to CSV
• Postgresql current_timestamp
• Postgresql listen_addresses
• PostgreSQL Order By

So in this tutorial, we have learned about “Postgresql row_number” using different Postgresql clauses like Group By, Order By, etc. we have covered the following topics.

• Postgresql row_number partition by
• Postgresql row_number vs rank
• Postgresql row_number in where clause
• Postgresql row_number without over
• Postgresql row_number group by
• Postgresql row_number without order
• Postgresql row_number start at 0
• Postgresql row_number over partition by
• Postgresql row_number over distinct
• Postgresql row_number order by partition