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  |  285
Oman    | 1981 |       330  |  285
... 

Two countries (Iran and Oman) are chosen from our dataset for the period 1978 to 1983. Keep in mind that there was a revolution going on in Iran in 1979, so this had some impact on the production of oil. The data reflects this.

What the query does is calculate the minimum production up to a certain point in our time series. At this point, it is a good way for SQL students to remember what an ORDER BY clause does inside an OVER clause. In this example, the PARTITION BY clause will create one group for each country and order data inside the group. The min function will loop over the sorted data and provide the required minimums.

If you are new to windowing functions, there is something you should be aware of. It really does make a difference, irrespective of whether you use an ORDER BY clause or not:

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

If the aggregate is used without ORDER BY, it will automatically take the minimum of the entire dataset inside your windows. This doesn't happen if there is an ORDER BY clause. In this case, it will always be the minimum up to this point, given the order you have defined.