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