Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT ROW_NUMBER() OVER w, RANK() OVER w
FROM employees
WINDOW w AS (ORDER BY salary DESC);

Try it

SQL One definition, three functions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Extending a named window, and several windows in one query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 in PARTITION BY. Write OVER w, not OVER (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 w with an ORDER BY in w is cumulative; ROW_NUMBER() OVER w is simply numbered.
  • Common mistake: repeating ORDER BY in OVER (w ORDER BY ...) when w already has one is an error.
  • Placement: WINDOW comes after GROUP BY / HAVING and before the final ORDER BY, LIMIT or set operators.