In PostgreSQL, we can use the row_number() function to get each row’s number within its partition. This allows us to create a column with incrementing row numbers that reset with each new partition.
The row_number() function is a window function that’s specifically designed to return the number of the current row within its partition, starting at 1 and incrementing sequentially.
Syntax
The syntax goes like this:
row_number() over_clauseThe OVER clause determines how the rowset is partitioned and ordered before the window function is applied.
Example
Here’s a basic example to demonstrate how it works:
SELECT
row_number() OVER ( ORDER BY productprice ),
vendorid,
productname,
productprice
FROM products;Result:
row_number | vendorid | productname | productprice
------------+----------+---------------------------------+--------------
1 | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99
2 | 1003 | Hammock | 10.00
3 | 1001 | Long Weight (green) | 11.99
4 | 1004 | Tea Pot | 12.45
5 | 1001 | Long Weight (blue) | 14.75
6 | 1001 | Left handed screwdriver | 25.99
7 | 1001 | Right handed screwdriver | 25.99
8 | 1002 | Sledge Hammer | 33.49
9 | 1003 | Straw Dog Box | 55.99
10 | 1003 | Chainsaw | 245.00
(10 rows)
If we omit the ORDER BY clause, the ordering is nondeterministic:
SELECT
row_number() OVER (),
vendorid,
productname,
productprice
FROM products;Result:
row_number | vendorid | productname | productprice
------------+----------+---------------------------------+--------------
1 | 1001 | Left handed screwdriver | 25.99
2 | 1001 | Right handed screwdriver | 25.99
3 | 1001 | Long Weight (blue) | 14.75
4 | 1001 | Long Weight (green) | 11.99
5 | 1002 | Sledge Hammer | 33.49
6 | 1003 | Chainsaw | 245.00
7 | 1003 | Straw Dog Box | 55.99
8 | 1003 | Hammock | 10.00
9 | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99
10 | 1004 | Tea Pot | 12.45
(10 rows)
Regardless, the row_number() function continues to do the same thing – return the row number within the partition.
We haven’t applied any partitioning here, and so the numbering increments throughout the whole rowset.
Partitioning
This example uses a PARTITION BY clause to partition the results by the vendor:
SELECT
row_number() OVER (
PARTITION BY p.vendorid
ORDER BY p.productprice
),
v.vendorname,
p.productname,
p.productprice
FROM products p INNER JOIN
vendors v ON p.vendorid = v.vendorid;Result:
row_number | vendorname | productname | productprice
------------+---------------+---------------------------------+--------------
1 | Mars Supplies | Long Weight (green) | 11.99
2 | Mars Supplies | Long Weight (blue) | 14.75
3 | Mars Supplies | Right handed screwdriver | 25.99
4 | Mars Supplies | Left handed screwdriver | 25.99
1 | Randy Roofers | Sledge Hammer | 33.49
1 | Pedal Medals | Hammock | 10.00
2 | Pedal Medals | Straw Dog Box | 55.99
3 | Pedal Medals | Chainsaw | 245.00
1 | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99
2 | Katty Kittens | Tea Pot | 12.45
(10 rows)
We can see that the row numbers reset with each partition.
In this case I performed a SQL join on the vendors table in order to return the vendor name instead of the vendor ID.
Changing the Order
Here’s what happens when we change the order to descending order:
SELECT
row_number() OVER (
PARTITION BY p.vendorid
ORDER BY p.productprice DESC
),
v.vendorname,
p.productname,
p.productprice
FROM products p INNER JOIN
vendors v ON p.vendorid = v.vendorid;Result:
row_number | vendorname | productname | productprice
------------+---------------+---------------------------------+--------------
1 | Mars Supplies | Left handed screwdriver | 25.99
2 | Mars Supplies | Right handed screwdriver | 25.99
3 | Mars Supplies | Long Weight (blue) | 14.75
4 | Mars Supplies | Long Weight (green) | 11.99
1 | Randy Roofers | Sledge Hammer | 33.49
1 | Pedal Medals | Chainsaw | 245.00
2 | Pedal Medals | Straw Dog Box | 55.99
3 | Pedal Medals | Hammock | 10.00
1 | Katty Kittens | Tea Pot | 12.45
2 | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99
(10 rows)
So the data within each partition is returned in a different order but the numbering remains the same (i.e. 1, 2, 3, etc).
Here it is again, but with row_number() applied to two different columns, each with different ordering:
SELECT
row_number() OVER (
PARTITION BY p.vendorid
ORDER BY p.productprice ASC
) AS "ASC",
row_number() OVER (
PARTITION BY p.vendorid
ORDER BY p.productprice DESC
) AS "DESC",
v.vendorname,
p.productname,
p.productprice
FROM products p INNER JOIN
vendors v ON p.vendorid = v.vendorid;Result:
ASC | DESC | vendorname | productname | productprice -----+------+---------------+---------------------------------+-------------- 1 | 4 | Mars Supplies | Long Weight (green) | 11.99 2 | 3 | Mars Supplies | Long Weight (blue) | 14.75 3 | 2 | Mars Supplies | Right handed screwdriver | 25.99 4 | 1 | Mars Supplies | Left handed screwdriver | 25.99 1 | 1 | Randy Roofers | Sledge Hammer | 33.49 1 | 3 | Pedal Medals | Hammock | 10.00 2 | 2 | Pedal Medals | Straw Dog Box | 55.99 3 | 1 | Pedal Medals | Chainsaw | 245.00 1 | 2 | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99 2 | 1 | Katty Kittens | Tea Pot | 12.45 (10 rows)
Here, the ASC column is numbered in ascending order, while DESC is in descending order.
Named Windows
We can move the window’s definition, or part of it, to a named window.
Example:
SELECT
row_number() OVER ( win
ORDER BY p.productprice ASC
) AS "ASC",
row_number() OVER ( win
ORDER BY p.productprice DESC
) AS "DESC",
v.vendorname,
p.productname,
p.productprice
FROM products p INNER JOIN
vendors v ON p.vendorid = v.vendorid
WINDOW win AS ( PARTITION BY p.vendorid );Result:
ASC | DESC | vendorname | productname | productprice -----+------+---------------+---------------------------------+-------------- 1 | 4 | Mars Supplies | Long Weight (green) | 11.99 2 | 3 | Mars Supplies | Long Weight (blue) | 14.75 3 | 2 | Mars Supplies | Right handed screwdriver | 25.99 4 | 1 | Mars Supplies | Left handed screwdriver | 25.99 1 | 1 | Randy Roofers | Sledge Hammer | 33.49 1 | 3 | Pedal Medals | Hammock | 10.00 2 | 2 | Pedal Medals | Straw Dog Box | 55.99 3 | 1 | Pedal Medals | Chainsaw | 245.00 1 | 2 | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99 2 | 1 | Katty Kittens | Tea Pot | 12.45 (10 rows)
Doing this saves us from having to replicate code across our SELECT list, but it still allows us to add clauses to that named window. In this case, we referenced the named window, but we also added the ORDER BY clause.
The OVER Clause is Required
When using the row_number() function, we must provide an OVER clause. This determines how the rowset is partitioned and ordered before the window function is applied.
Omitting the OVER clause results in an error:
SELECT
row_number(),
vendorid,
productname,
productprice
FROM products;Result:
ERROR: window function row_number requires an OVER clause