NAMED WINDOW CLAUSEQuery Techniques
Buy Me A Coffee

Define a reusable window specification with a name in a WINDOW clause, then reference it by name across multiple window functions in the same query. Eliminates duplicated OVER(...) definitions.

Defines window specifications once and reuses them across multiple window functions in the same query, reducing repetition. Particularly useful when the same partition and ordering appears in multiple functions. Improves readability and maintainability.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 WINDOW clause supported since MySQL 8.0. Named windows can be referenced by name in OVER (name) or extended in OVER (name ORDER BY ...) to add or modify clauses. A named window cannot be redefined differently in the same query. ORDER BY and PARTITION BY defined in the named window can be extended but not overridden by the referencing OVER clause.
PostgreSQL ✓ Supported 8.4 WINDOW clause fully supported since PostgreSQL 8.4 — the earliest of any engine. Named windows can be extended with additional ORDER BY or frame clauses in the referencing OVER clause but cannot change the existing PARTITION BY or ORDER BY. Multiple named windows can be defined, and one named window can reference another (inheritance).
SQL Server ✗ Not Supported — SQL Server does not support the WINDOW clause. Named window definitions cannot be declared and reused. Each window function must repeat its full OVER (...) specification. If multiple window functions share the same OVER clause, the optimizer may still recognize and reuse the window internally, but the syntax must be duplicated.
Oracle ✗ Not Supported — Oracle does not support the SQL standard WINDOW clause. Each window function requires its own full OVER (...) specification. The optimizer may internally recognize and share window computation for identical OVER clauses, but the syntax still requires repetition.
SQLite ✓ Supported 3.28.0 WINDOW clause supported since SQLite 3.28.0 (2019-04-16). Standard syntax — named windows can be defined and referenced, and they can be extended (but not overridden) in the referencing OVER clause.

Details

The WINDOW clause is part of the SQL standard and is supported by PostgreSQL (8.4+), MySQL (8.0+), and SQLite (3.28.0+). SQL Server and Oracle omit it entirely — both require repeating the full OVER() specification for every window function. The practical impact is significant: a query with five window functions all sharing the same partition/order must duplicate that definition five times in SQL Server and Oracle. PostgreSQL supports named window inheritance — one named window can build on another — which MySQL and SQLite do not support. Even without the WINDOW clause, SQL Server and Oracle's optimizers typically recognize identical OVER() clauses and compute the window only once in the execution plan.

Standard Syntax

SELECT name, salary, RANK() OVER w AS rnk, DENSE_RANK() OVER w AS dense_rnk, AVG(salary) OVER w AS dept_avg FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Not supported Oracle: Not supported SQLite: Since 3.28.0

Per-Database Syntax & Notes

MySQL

WINDOW clause supported since MySQL 8.0. Named windows can be referenced by name in OVER (name) or extended in OVER (name ORDER BY ...) to add or modify clauses. A named window cannot be redefined differently in the same query. ORDER BY and PARTITION BY defined in the named window can be extended but not overridden by the referencing OVER clause.

SELECT name, dept, salary, RANK() OVER w AS dept_rank, DENSE_RANK() OVER w AS dept_dense_rank, AVG(salary) OVER w AS dept_avg_salary, SUM(salary) OVER dept_total AS dept_total_salary FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC), dept_total AS (PARTITION BY dept); -- Extending a named window: SELECT name, ROW_NUMBER() OVER (w ORDER BY hire_date) AS hire_order FROM employees WINDOW w AS (PARTITION BY dept); -- hire_date ORDER BY extends the named window w

PostgreSQL

WINDOW clause fully supported since PostgreSQL 8.4 — the earliest of any engine. Named windows can be extended with additional ORDER BY or frame clauses in the referencing OVER clause but cannot change the existing PARTITION BY or ORDER BY. Multiple named windows can be defined, and one named window can reference another (inheritance).

SELECT name, dept, salary, RANK() OVER w AS dept_rank, DENSE_RANK() OVER w AS dept_dense_rank, AVG(salary) OVER w AS dept_avg, SUM(salary) OVER pt AS dept_total FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC), pt AS (PARTITION BY dept); -- Window inheritance (one named window builds on another): SELECT name, ROW_NUMBER() OVER w1 AS rn, RANK() OVER w2 AS rnk FROM employees WINDOW w1 AS (PARTITION BY dept), w2 AS (w1 ORDER BY salary DESC); -- w2 inherits the PARTITION BY from w1

SQL Server

SQL Server does not support the WINDOW clause. Named window definitions cannot be declared and reused. Each window function must repeat its full OVER (...) specification. If multiple window functions share the same OVER clause, the optimizer may still recognize and reuse the window internally, but the syntax must be duplicated.

-- Must repeat the OVER clause for each window function: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_dense_rank, AVG(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_avg FROM employees; -- A CTE can reduce repetition slightly: WITH windowed AS ( SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_dense_rank FROM employees ) SELECT * FROM windowed;

Oracle

Oracle does not support the SQL standard WINDOW clause. Each window function requires its own full OVER (...) specification. The optimizer may internally recognize and share window computation for identical OVER clauses, but the syntax still requires repetition.

-- Must repeat OVER for each function: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_dense_rank, AVG(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_avg FROM employees; -- Factor with a subquery to avoid repeating columns: SELECT name, dept, salary, dept_rank, dept_avg FROM ( SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees );

SQLite

WINDOW clause supported since SQLite 3.28.0 (2019-04-16). Standard syntax — named windows can be defined and referenced, and they can be extended (but not overridden) in the referencing OVER clause.

SELECT name, dept, salary, RANK() OVER w AS dept_rank, AVG(salary) OVER w AS dept_avg FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC); -- Multiple named windows: SELECT name, ROW_NUMBER() OVER full_order AS overall_rn, ROW_NUMBER() OVER dept_order AS dept_rn FROM employees WINDOW full_order AS (ORDER BY salary DESC), dept_order AS (PARTITION BY dept ORDER BY salary DESC);