10.3. Ordering

The `ORDER BY` sub-clause can be used with or without partitions. The `ORDER BY` clause within `OVER` specifies the order in which the window function will process rows. This order does not have to be the same as the order rows appear in the output.

There is an important concept associated with window functions: for each row there is a set of rows in its partition called the window frame. By default, when specifying `ORDER BY`, the frame consists of all rows from the beginning of the partition to the current row and rows equal to the current `ORDER BY` expression. Without `ORDER BY`, the default frame consists of all rows in the partition.

As a result, for standard aggregate functions, the `ORDER BY` clause produces partial aggregation results as rows are processed.

Example

```  |`select`
|`    id,`
|`    salary,`
|`    sum(salary) over (order by salary) cumul_salary`
|`  from employee`
|`  order by salary;`
```

Results

```  |`id  salary  cumul_salary`
|`--  ------  ------------`
|`3     8.00          8.00`
|`4     9.00         17.00`
|`1    10.00         37.00`
|`5    10.00         37.00`
|`2    12.00         49.00`
```

Then `cumul_salary` returns the partial/accumulated (or running) aggregation (of the `SUM` function). It may appear strange that 37.00 is repeated for the ids 1 and 5, but that is how it should work. The `ORDER BY` keys are grouped together, and the aggregation is computed once (but summing the two 10.00). To avoid this, you can add the `ID` field to the end of the `ORDER BY` clause.

It’s possible to use multiple windows with different orders, and `ORDER BY` parts like `ASC`/`DESC` and `NULLS FIRST/LAST`.

With a partition, `ORDER BY` works the same way, but at each partition boundary the aggregation is reset.

All aggregation functions can use `ORDER BY`, except for `LIST()`.