Window ordering

Window ordering specifies how to sort rows that are supplied to the 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 [ 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:

• RANK

• DENSE_RANK

• LEAD

• LAG

• PERCENT_RANK

• CUME_DIST

• NTILE

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

• PERCENTILE_CONT

• PERCENTILE_DISC

• MEDIAN

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

The following query orders sales inside each state partition:

=> 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)

Example 2

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 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)