Mastering PostgreSQL 12 - Third Edition

Ordering data inside a window

A PARTITION BY clause is not the only possible thing you can put into an OVER clause. Sometimes, it is necessary to sort data inside a window. ORDER BY will provide data to your aggregate functions in a certain way. Here is an example:

test=# SELECT country, year, production, 
              min(production) OVER (PARTITION BY country ORDER BY year)  
FROM   t_oil 
WHERE  year BETWEEN 1978 AND 1983        AND country IN ('Iran', 'Oman'); country | year | production | min 
---------+-----+------------+------ Iran    | 1978 |       5302 | 5302Iran    | 1979 |       3218 | 3218Iran    | 1980 |       1479 | 1479Iran    | 1981 |       1321 | 1321Iran    | 1982 |       2397 | 1321Iran    | 1983 |       2454 | 1321Oman    | 1978 |       314  |  314Oman    | 1979 |       295  |  295Oman    | 1980 |       285  |  285Oman    | 1981 |