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 unique integer to each row in a result set. The following illustrates syntax of the ROW_NUMBER() function:

ROW_NUMBER() OVER(

[PARTITION BY column_1, column_2,…]

[ORDER BY column_3,column_4,…]

)

The set of rows that the ROW_NUMBER() function operates on 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.

PostgreSQL ROW_NUMBER() function examples

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

products_product_groups_tables

The following is the data in the products table.

product_id | product_name | price | group_id

------------+--------------------+---------+----------

1 | Microsoft Lumia | 300.00 | 1

2 | HTC One | 400.00 | 1

3 | Nexus | 500.00 | 1

4 | iPhone | 800.00 | 1

5 | HP Elite | 900.00 | 2

6 | Lenovo Thinkpad | 1100.00 | 2

7 | Sony VAIO | 600.00 | 2

8 | Dell Vostro | 600.00 | 2

9 | iPad | 700.00 | 3

10 | Kindle Fire | 300.00 | 3

11 | Samsung Galaxy Tab | 800.00 | 3

(11 rows)

See the following query.

SELECT

product_id,

product_name,

group_id,

ROW_NUMBER () OVER (ORDER BY product_id)

FROM

products;

product_id | product_name | group_id | row_number

------------+--------------------+----------+------------

1 | Microsoft Lumia | 1 | 1

2 | HTC One | 1 | 2

3 | Nexus | 1 | 3

4 | iPhone | 1 | 4

5 | HP Elite | 2 | 5

6 | Lenovo Thinkpad | 2 | 6

7 | Sony VAIO | 2 | 7

8 | Dell Vostro | 2 | 8

9 | iPad | 3 | 9

10 | Kindle Fire | 3 | 10

11 | Samsung Galaxy Tab | 3 | 11

(11 rows)

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.

SELECT

product_id,

product_name,

group_id,

ROW_NUMBER () OVER (ORDER BY product_name)

FROM

products;

product_id | product_name | group_id | row_number

------------+--------------------+----------+------------

8 | Dell Vostro | 2 | 1

5 | HP Elite | 2 | 2

2 | HTC One | 1 | 3

9 | iPad | 3 | 4

4 | iPhone | 1 | 5

10 | Kindle Fire | 3 | 6

6 | Lenovo Thinkpad | 2 | 7

1 | Microsoft Lumia | 1 | 8

3 | Nexus | 1 | 9

11 | Samsung Galaxy Tab | 3 | 10

7 | Sony VAIO | 2 | 11

(11 rows)

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;

product_id | product_name | group_id | row_number

------------+--------------------+----------+------------

2 | HTC One | 1 | 1

4 | iPhone | 1 | 2

1 | Microsoft Lumia | 1 | 3

3 | Nexus | 1 | 4

8 | Dell Vostro | 2 | 1

5 | HP Elite | 2 | 2

6 | Lenovo Thinkpad | 2 | 3

7 | Sony VAIO | 2 | 4

9 | iPad | 3 | 1

10 | Kindle Fire | 3 | 2

11 | Samsung Galaxy Tab | 3 | 3

(11 rows)

PostgreSQL ROW_NUMBER() function and DISTINCT

The following query uses the ROW_NUMBER() function to assign integers to the distinct prices from the products table.

SELECT DISTINCT

price,

ROW_NUMBER () OVER (ORDER BY price)

FROM

products

ORDER BY

price;

price | row_number

---------+------------

300.00 | 1

300.00 | 2

400.00 | 3

500.00 | 4

600.00 | 5

600.00 | 6

700.00 | 7

800.00 | 8

800.00 | 9

900.00 | 10

1100.00 | 11

(11 rows)

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

To solved 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:

WITH prices AS (

SELECT DISTINCT

price

FROM

products

) SELECT

price,

ROW_NUMBER () OVER (ORDER BY price)

FROM

prices;

price | row_number

---------+------------

300.00 | 1

400.00 | 2

500.00 | 3

600.00 | 4

700.00 | 5

800.00 | 6

900.00 | 7

1100.00 | 8

(8 rows)

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.

SELECT

price,

ROW_NUMBER () OVER (ORDER BY price)

FROM

(

SELECT DISTINCT

price

FROM

products

) prices;

price | row_number

---------+------------

300.00 | 1

400.00 | 2

500.00 | 3

600.00 | 4

700.00 | 5

800.00 | 6

900.00 | 7

1100.00 | 8

(8 rows)

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:

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;

product_id | product_name | price | row_number

------------+--------------------+---------+------------

10 | Kindle Fire | 300.00 | 6

6 | Lenovo Thinkpad | 1100.00 | 7

1 | Microsoft Lumia | 300.00 | 8

3 | Nexus | 500.00 | 9

11 | Samsung Galaxy Tab | 800.00 | 10

(5 rows)

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.

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

)

product_id | product_name | price | group_id

------------+--------------------+--------+----------

4 | iPhone | 800.00 | 1

11 | Samsung Galaxy Tab | 800.00 | 3

(2 rows)

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.