Summary: in this tutorial, you will learn how to use the PostgreSQL ROW_NUMBER[]
function to assign a unique integer value to each row in a result set.
Introduction to the PostgreSQL ROW_NUMBER[]
function
The ROW_NUMBER[]
function is a window function that assigns a sequential integer to each row in a result set. The following illustrates
the syntax of the ROW_NUMBER[]
function:
Code language: SQL [Structured Query Language] [sql]
ROW_NUMBER[] OVER[ [PARTITION BY column_1, column_2,…] [ORDER BY column_3,column_4,…] ]
The set of rows on which the ROW_NUMBER[]
function operates is called a window.
The PARTITION BY
clause divides the window into smaller sets or partitions. If you specify the PARTITION BY
clause, the row number for each partition starts with one and increments by one.
Because the PARTITION BY
clause is optional to the ROW_NUMBER[]
function, therefore you can omit it, and ROW_NUMBER[]
function will treat the whole window as a partition.
The
ORDER BY
clause inside the OVER
clause determines the order in which the numbers are assigned.
We will use the products
table created in the PostgreSQL window function tutorial to demonstrate the functionality of the ROW_NUMBER[]
function.
The following is the data in the products
table:
See the following query.
Code language: SQL [Structured Query Language] [sql]
SELECT product_id, product_name, group_id, ROW_NUMBER [] OVER [ORDER BY product_id] FROM products;
Because we did not use the PARTITION BY
clause, the ROW_NUMBER[]
function considers the whole result set as a partition.
The ORDER BY
clause sorts the result set by product_id
, therefore, the ROW_NUMBER[]
function assigns integer values to the rows
based on the product_id
order.
In the following query, we change the column in the ORDER BY
clause to product_name, the ROW_NUMBER[]
function assigns the integer values to each row based on the product name order.
Code language: SQL [Structured Query Language] [sql]
SELECT product_id, product_name, group_id, ROW_NUMBER [] OVER [ ORDER BY product_name ] FROM products;
In the following query, we use the PARTITION BY
clause to divide the window into subsets based on the values in the group_id
column. In this case, the
ROW_NUMBER[]
function assigns one to the starting row of each partition and increases by one for the next row within the same partition.
The ORDER BY
clause sorts the rows in each partition by the values in the product_name
column.
SELECT product_id, product_name, group_id, ROW_NUMBER [] OVER [ PARTITION BY group_id ORDER BY product_name ] FROM products;
Code language: SQL [Structured Query Language] [sql]
PostgreSQL ROW_NUMBER[]
function and DISTINCT
operator
The following query uses the ROW_NUMBER[]
function to assign integers to the
distinct prices from the products
table.
Code language: SQL [Structured Query Language] [sql]
SELECT DISTINCT price, ROW_NUMBER [] OVER [ORDER BY price] FROM products ORDER BY price;
However, the result is not
expected because it includes duplicate prices. The reason is that the ROW_NUMBER[]
operates on the result set before the DISTINCT
is applied.
To solve this problem, we can get a list of distinct prices in a CTE, the apply the ROW_NUMBER[]
function in the outer query as follows:
Code language: SQL [Structured Query Language] [sql]
WITH prices AS [ SELECT DISTINCT price FROM products ] SELECT price, ROW_NUMBER [] OVER [ORDER BY price] FROM prices;
Or we can use a subquery in the FROM
clause to get a list of unique price, and then apply the ROW_NUMBER[]
function in the outer query.
Code language: SQL [Structured Query Language] [sql]
SELECT price, ROW_NUMBER [] OVER [ORDER BY price] FROM [ SELECT DISTINCT price FROM products ] prices;
Using 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 the LIMIT clause, you can also use the ROW_NUMBER[]
function for the pagination.
For example, the following query selects the five rows starting at row number 6:
Code language: SQL [Structured Query Language] [sql]
SELECT * FROM [ SELECT product_id, product_name, price, ROW_NUMBER [] OVER [ORDER BY product_name] FROM products ] x WHERE ROW_NUMBER BETWEEN 6 AND 10;
Using 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.
Code language: SQL [Structured Query Language] [sql]
SELECT * FROM products WHERE price = [ SELECT price FROM [ SELECT price, ROW_NUMBER [] OVER [ ORDER BY price DESC ] nth FROM [ SELECT DISTINCT [price] FROM products ] prices ] sorted_prices WHERE nth = 3 ];
In this tutorial, we have shown you how to use the PostgreSQL ROW_NUMBER[]
function to assign integer values to rows in a result set.
Was this tutorial helpful ?