Window Ordering

Window ordering specifies how to sort rows that are supplied to the analytic function. You specify window ordering through an ORDER BY clause in the function's OVER clause, as shown below. If the OVER clause includes a window partition clause, rows are sorted within each partition. An window order clause also creates a default window frame if none is explicitly specified.

The window order clause only specifies order within a window result set. The query can have its own ORDER BY clause outside the OVER clause. This has precedence over the window order clause and orders the final result set.

Specifying Window Order

You specify a window frame in the analytic function's OVER clause, as shown below:

ORDER BY { expression[ sort-qualifiers ] } [,…]

sort‑qualifiers:

   { ASC | DESC [ NULLS { FIRST | LAST | AUTO } ] }

For syntax details, see Window Order Clause.

Analytic Function Usage

Analytic aggregation functions such as SUM support window order clauses.

Required Usage

The following functions require a window order clause:

Invalid Usage

You cannot use a window order clause with the following functions:

Examples

The examples below use the allsales table schema:

CREATE TABLE allsales(state VARCHAR(20), name VARCHAR(20), sales INT);
INSERT INTO allsales VALUES('MA', 'A', 60);
INSERT INTO allsales VALUES('NY', 'B', 20);
INSERT INTO allsales VALUES('NY', 'C', 15);
INSERT INTO allsales VALUES('MA', 'D', 20);
INSERT INTO allsales VALUES('MA', 'E', 50);
INSERT INTO allsales VALUES('NY', 'F', 40);
INSERT INTO allsales VALUES('MA', 'G', 10);
COMMIT;
Example 1 Example 2

The following query orders sales inside each state partition:

The following query's final ORDER BY clause sorts results by name:

=> SELECT state, sales, name, RANK() OVER(
  PARTITION BY state 
  ORDER BY sales) AS RANK
  FROM allsales;
 state | sales | name | RANK 
-------+-------+------+----------
 MA    |    10 | G    |        1
 MA    |    20 | D    |        2
 MA    |    50 | E    |        3
 MA    |    60 | A    |        4
--------------------------------- 
 NY    |    15 | C    |        1
 NY    |    20 | B    |        2
 NY    |    40 | F    |        3
(7 rows)
=> SELECT state, sales, name, RANK() OVER(
  PARTITION by state 
  ORDER BY sales) AS RANK
  FROM allsales ORDER BY name;
 state | sales | name | RANK 
-------+-------+------+----------
 MA    |    60 | A    |        4
 NY    |    20 | B    |        2
 NY    |    15 | C    |        1
 MA    |    20 | D    |        2
 MA    |    50 | E    |        3
 NY    |    40 | F    |        3
 MA    |    10 | G    |        1
(7 rows)