In PostgreSQL the first_value() function returns the value from the first row in the current window frame.
We can use this function to get the value from the first row in a result set, or from the first row in the current partition, or some other window frame that’s been specified.
Syntax
The syntax goes like this:
first_value ( value ) The value argument is required. It’s the expression that is evaluated at the first row of the window frame.
Example
Here’s an example to demonstrate:
SELECT
vendorid,
productname,
productprice,
first_value( productprice ) OVER ( ORDER BY productprice )
FROM products;Result:
vendorid | productname | productprice | first_value
----------+---------------------------------+--------------+-------------
1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | 9.99
1003 | Hammock | 10.00 | 9.99
1001 | Long Weight (green) | 11.99 | 9.99
1004 | Tea Pot | 12.45 | 9.99
1001 | Long Weight (blue) | 14.75 | 9.99
1001 | Left handed screwdriver | 25.99 | 9.99
1001 | Right handed screwdriver | 25.99 | 9.99
1002 | Sledge Hammer | 33.49 | 9.99
1003 | Straw Dog Box | 55.99 | 9.99
1003 | Chainsaw | 245.00 | 9.99
We can see that the first_value column returns the value from the first row of the productprice column. That’s because I specified productprice as the argument.
Change the Order
In the previous example I sorted the results by the productprice column in ascending order. Let’s change it to descending order:
SELECT
vendorid,
productname,
productprice,
first_value( productprice ) OVER ( ORDER BY productprice DESC )
FROM products;Result:
vendorid | productname | productprice | first_value
----------+---------------------------------+--------------+-------------
1003 | Chainsaw | 245.00 | 245.00
1003 | Straw Dog Box | 55.99 | 245.00
1002 | Sledge Hammer | 33.49 | 245.00
1001 | Left handed screwdriver | 25.99 | 245.00
1001 | Right handed screwdriver | 25.99 | 245.00
1001 | Long Weight (blue) | 14.75 | 245.00
1004 | Tea Pot | 12.45 | 245.00
1001 | Long Weight (green) | 11.99 | 245.00
1003 | Hammock | 10.00 | 245.00
1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | 245.00
This time the highest price is at the first row. Therefore, first_value() returns that price.
Partition the Results
Let’s add a PARTITION BY clause to partition by vendor:
SELECT
v.vendorname,
p.productname,
p.productprice,
first_value( p.productprice ) OVER (
PARTITION BY p.vendorid
ORDER BY p.productprice
)
FROM products p INNER JOIN
vendors v ON p.vendorid = v.vendorid;Result:
vendorname | productname | productprice | first_value ---------------+---------------------------------+--------------+------------- Mars Supplies | Long Weight (green) | 11.99 | 11.99 Mars Supplies | Long Weight (blue) | 14.75 | 11.99 Mars Supplies | Right handed screwdriver | 25.99 | 11.99 Mars Supplies | Left handed screwdriver | 25.99 | 11.99 Randy Roofers | Sledge Hammer | 33.49 | 33.49 Pedal Medals | Hammock | 10.00 | 10.00 Pedal Medals | Straw Dog Box | 55.99 | 10.00 Pedal Medals | Chainsaw | 245.00 | 10.00 Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99 | 9.99 Katty Kittens | Tea Pot | 12.45 | 9.99
We can see that the first_value column resets with each new partition.
By the way, in this example I also ran a SQL join between the products table and the vendors table in order to get each vendor’s name instead of just the ID.
Using a Frame Clause
We can use a frame clause to explicitly specify a window frame to use:
SELECT
vendorid,
productname,
productprice,
first_value( productprice ) OVER (
ORDER BY productprice
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
FROM products;Result:
vendorid | productname | productprice | first_value
----------+---------------------------------+--------------+-------------
1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | 9.99
1003 | Hammock | 10.00 | 9.99
1001 | Long Weight (green) | 11.99 | 10.00
1004 | Tea Pot | 12.45 | 11.99
1001 | Long Weight (blue) | 14.75 | 12.45
1001 | Left handed screwdriver | 25.99 | 14.75
1001 | Right handed screwdriver | 25.99 | 25.99
1002 | Sledge Hammer | 33.49 | 25.99
1003 | Straw Dog Box | 55.99 | 33.49
1003 | Chainsaw | 245.00 | 55.99
Here, I specified ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for the frame clause which means that for each row, the window frame consists of three rows; the preceding row, the current row, and the next row. We can see how that affects the outcome of the first_value column. Given we’re sorting it in ascending order, first_value always the lowest value from the frame.
Let’s change to descending order:
SELECT
vendorid,
productname,
productprice,
first_value( productprice ) OVER (
ORDER BY productprice DESC
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
FROM products;Result:
vendorid | productname | productprice | first_value
----------+---------------------------------+--------------+-------------
1003 | Chainsaw | 245.00 | 245.00
1003 | Straw Dog Box | 55.99 | 245.00
1002 | Sledge Hammer | 33.49 | 55.99
1001 | Left handed screwdriver | 25.99 | 33.49
1001 | Right handed screwdriver | 25.99 | 25.99
1001 | Long Weight (blue) | 14.75 | 25.99
1004 | Tea Pot | 12.45 | 14.75
1001 | Long Weight (green) | 11.99 | 12.45
1003 | Hammock | 10.00 | 11.99
1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | 10.00
This time the first_value is always the highest value of the current frame.
Removing the OVER Clause
The OVER clause is required when using nonaggregate window functions such as first_value(). Removing it results in an error:
SELECT
vendorid,
productname,
productprice,
first_value( productprice )
FROM products;Result:
ERROR: window function first_value requires an OVER clause
LINE 5: first_value( productprice )
^