Should PARTITION BY or ORDER BY come first in an OVER clause?

PARTITION BY always comes first, then ORDER BY, then the optional frame clause. This is the order required by the SQL standard and enforced by every major engine. Formatting them top-to-bottom in that same order makes the window easy to read and matches how the database evaluates it.

What is a named WINDOW clause and which databases support it?

A named WINDOW clause defines a window specification once at the bottom of the query and lets multiple functions reference it by name, like OVER w. It's supported in PostgreSQL, MySQL 8.0+, MariaDB, and SQL Server 2022 and later. It removes duplication when several columns share the same partition and ordering.

Do I need to specify a window frame (ROWS BETWEEN)?

Not always, but you should when using ORDER BY with aggregate window functions like SUM or AVG. Without an explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats tied ORDER BY values as one group and can give unexpected running totals. Writing ROWS BETWEEN explicitly removes the ambiguity.

Does formatting a window function change its result?

No. Line breaks, indentation, and whitespace inside the OVER clause are ignored by the parser, so reformatting never changes results. Only changing the actual sub-clauses — the partition columns, ordering, or frame — affects output. Formatting is purely for human readability.

How to Format SQL Window Functions for Readability

sqlfmt▍

How to Format SQL Window Functions for Readability — sqlfmt

The cleanest way to format a SQL window function is to keep short OVER clauses on one line and break long ones — anything with both PARTITION BY and ORDER BY — across multiple indented lines. A window function packs partitioning, ordering, and an optional frame into a single OVER(...) expression, and when you jam all of that onto one line it becomes unreadable fast.

This guide gives you a consistent house style for the OVER clause: when to keep it inline, how to break PARTITION BY and ORDER BY onto their own lines, how to format window frames, and how to deduplicate repeated windows with a named WINDOW clause. Every example runs on PostgreSQL, MySQL 8+, and SQL Server.

Keep short OVER clauses on one line

If the OVER clause has just one sub-clause — only a PARTITION BY, or only an ORDER BY — leave it inline. Wrapping a trivial window across three lines adds noise without adding clarity.

Inline is fine for a single sub-clause

SELECT employee_id,
       department_id,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
       AVG(salary)  OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

After: PARTITION BY and ORDER BY each on their own line

SELECT employee_id,
       department_id,
       salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Running total with a clearly framed window

SELECT order_date,
       amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;

One named window, reused across functions

SELECT employee_id,
       department_id,
       salary,
       RANK()       OVER w AS dept_rank,
       DENSE_RANK() OVER w AS dense_rank,
       LAG(salary)  OVER w AS prev_salary
FROM employees
WINDOW w AS (
    PARTITION BY department_id
    ORDER BY salary DESC
);

How to Format SQL Window Functions Readably

Quick answer

FAQs

Should PARTITION BY or ORDER BY come first in an OVER clause?

What is a named WINDOW clause and which databases support it?

Do I need to specify a window frame (ROWS BETWEEN)?

Does formatting a window function change its result?

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

Keep short OVER clauses on one line

Break long OVER clauses onto their own lines

Format window frames on their own line

Deduplicate repeated windows with a named WINDOW clause

Align column aliases and stack the SELECT list

Checklist: formatting a window function

Wrapping up

Quick answer

FAQs

Should PARTITION BY or ORDER BY come first in an OVER clause?

What is a named WINDOW clause and which databases support it?

Do I need to specify a window frame (ROWS BETWEEN)?

Does formatting a window function change its result?

Keep reading

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

Keep short OVER clauses on one line

Break long OVER clauses onto their own lines

Format window frames on their own line

Deduplicate repeated windows with a named WINDOW clause

Align column aliases and stack the SELECT list

Checklist: formatting a window function

Wrapping up