Mastering PostgreSQL 12 - Third Edition

The first_value(), nth_value(), and last_value() functions

Sometimes, it is necessary to calculate data based on the first value of a data window. Unsurprisingly, the function to do that is first_value():

test=# SELECT year,  production, 
              first_value(production) OVER (ORDER BY year) 
       FROM   t_oil 
       WHERE  country = 'Canada'  
       LIMIT  4;  year  | production | first_value 
-------+------------+------------- 
 1965  |        920 |         920 
 1966  |       1012 |         920 
 1967  |       1106 |         920 
 1968  |       1194 |         920  
(4 rows) 

Again, a sort order is needed to tell the system where the first value actually is. PostgreSQL will then put the same value into the last column. If you want to find the last value in the window, simply use the last_value() function instead of the first_value() function. ...