SQL Reference ยท Window Functions
SQL WINDOW clause (named windows)
Reference for the SQL WINDOW clause: define a window once, reuse it by name in several OVER clauses, extend a named window, and see which databases support it.
The WINDOW clause lets you give a window definition a name and reuse it. When several columns of one query use the same PARTITION BY and ORDER BY, repeating them is noisy and easy to get wrong. With a named window you write the definition once, after HAVING, and refer to it with OVER name.
A name can also be the starting point for a slightly different window: OVER (w ROWS BETWEEN ...) adds a frame to a named window, and one named window can build on another.
SELECT ROW_NUMBER() OVER w, RANK() OVER w FROM employees WINDOW w AS (ORDER BY salary DESC);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT ... FROM ... [ WHERE ... ] [ GROUP BY ... ] [ HAVING ... ] WINDOW window_name AS ( window_definition ) [, window_name2 AS ( ... ) ] [ ORDER BY ... ] function(...) OVER window_name function(...) OVER ( window_name [ORDER BY ...] [frame] )
| Part | Description |
|---|---|
window_name |
Any identifier. Names are local to the query. |
window_definition |
The usual content of OVER (...): PARTITION BY, ORDER BY, frame. It may start with the name of an earlier window in the same WINDOW clause. |
OVER window_name |
Uses the window exactly as defined. Written without parentheses. |
OVER (window_name ...) |
Copies the named window and adds clauses to it. |
Inheritance rules: a derived window cannot repeat PARTITION BY; it may add an ORDER BY only if the base window has none; and it may add a frame only if the base window has none.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
WINDOW clause |
3.28+ | Yes | 8.0+ | 2022+ | Not in older releases |
| Extend a named window | Yes | Yes | Yes | Yes (2022+) | n/a |
| Clause position | After HAVING |
After HAVING |
After HAVING |
After HAVING |
n/a |
-- Where WINDOW is missing (older SQL Server, Oracle): repeat the definition SELECT ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees;
Usage notes
- Pure convenience. A named window gives the same result as writing the definition out; it only shortens and clarifies the query.
- The name goes with
OVER, not inPARTITION BY. WriteOVER w, notOVER (w)unless you add clauses (the parenthesized form with only a name also works in most databases, but the short form is clearer). - Windows can be unused. A defined window nobody refers to is accepted.
- Frames inside a shared window apply to ranking and offset functions as well, but those functions ignore frames. A
SUM(...) OVER wwith anORDER BYinwis cumulative;ROW_NUMBER() OVER wis simply numbered. - Common mistake: repeating
ORDER BYinOVER (w ORDER BY ...)whenwalready has one is an error. - Placement:
WINDOWcomes afterGROUP BY/HAVINGand before the finalORDER BY,LIMITor set operators.