← All notes

Short answer

A window function computes across related rows while still returning one result per input row. You always write an OVER clause. [S1][S2]

Sketch

SELECT dept, name, salary,
       rank() OVER (PARTITION BY dept ORDER BY salary DESC)
FROM employees;

Points

• Unlike grouped aggregates, window functions do not collapse rows. [S1]
• Without PARTITION BY, the window is the whole result set. [S1]
• Built-in window functions need window syntax (OVER). [S2]
• Ordinary aggregates can also be used as window functions with OVER. [S2]

Claims

• id: C1 claim: A window function calculates across a set of rows. source: S1 evidence: "A window function performs a calculation across a set of table rows"
• id: C2 claim: Window functions do not collapse rows into one output. source: S1 evidence: "window functions do not cause rows to become grouped into a single output row"
• id: C3 claim: Built-in window functions require an OVER clause. source: S2 evidence: "an OVER clause is required"
• id: C4 claim: Ordinary aggregates can act as window functions. source: S2 evidence: "ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates) can be used as a window"

Sources

• [S1] PostgreSQL docs 3.5: Window Functions. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/tutorial-window.html . License: PostgreSQL License. Accessed 2026-11-05.
• [S2] Window Functions. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/functions-window.html . License: PostgreSQL License. Accessed 2026-11-05.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

Source notice: PostgreSQL documentation, Portions Copyright (c) 1996-2026, The PostgreSQL Global Development Group; Portions Copyright (c) 1994, The Regents of the University of California. Used under the PostgreSQL License (https://www.postgresql.org/about/licence/).

@misc{iil_note_m9s2dh61,
  author = {{iinventedlight}},
  title = {{PostgreSQL window functions: OVER, PARTITION BY, ORDER BY}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-window-functions/},
  note = {Permanent ID iil:note:m9s2dh61. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-window-functions.md.