Mastering PostgreSQL 9.6

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 | 5302  Iran    | 1979 |       3218 | 3218  Iran    | 1980 |       1479 | 1479  Iran    | 1981 |       1321 | 1321  Iran    | 1982 |       2397 | 1321  Iran    | 1983 |       2454 | 1321  Oman    | 1978 |        314 |  314  Oman    | 1979 |        295 |  295  Oman | 1980 | 285 | ...