SQL window functions calculate across related rows while keeping each original row in the result. Use OVER to define the calculation’s scope; unlike a grouped aggregate, a window function adds a value to detail rows instead of replacing them with one row per group.
What makes a function a window function?
The defining syntax is an OVER clause immediately after the function call. PostgreSQL’s official tutorial puts it this way: “A window function call always contains an OVER clause directly following the window function’s name and argument(s).” (PostgreSQL tutorial.)
As an Amazon Associate I earn from qualifying purchases.
For example, avg(salary) OVER (PARTITION BY department) computes an average for each department and makes that value available beside each employee’s salary. The employees remain separate rows. By contrast, an ordinary query using GROUP BY department with avg(salary) returns a department-level result rather than each employee row.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteHow the OVER clause controls the calculation
Partition: where the calculation restarts
PARTITION BY divides the rows into calculation groups. A department partition, for instance, calculates separately for each department. Without PARTITION BY, all rows available to the window function belong to one partition. Partitioning changes the calculation’s scope, not the number of detail rows returned.
#1 Best Overall
Ordering: the sequence used by the calculation
ORDER BY inside OVER defines calculation order; it does not guarantee the order in which the final query displays rows. Add a query-level ORDER BY when presentation order matters.
For functions such as row_number, rows tied on every expression in the window ordering can be numbered in an unspecified order. Add a stable tie-breaker—often a unique key—when repeatable numbering matters. The example below uses employee_id for that purpose, assuming it is unique.
Frame: which rows in the partition are considered
A frame is the subset of a partition considered for a frame-sensitive calculation on the current row. In PostgreSQL, when a window has an ORDER BY but no explicit frame, the default extends from the start of the partition through the current row and any peers tied on the ordering expressions. As a result, an ordered sum commonly acts as a running total; rows tied on the ordering values receive the same peer-inclusive cumulative result. See the PostgreSQL 17 window-function reference.
If you mean to aggregate across the whole partition, either omit the window ordering or specify a frame that reaches the end of the partition, such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. An explicit frame makes the intended scope clear and avoids accidentally getting a cumulative result.
Examples in PostgreSQL
Show a department average beside every employee
SELECT department,
employee_id,
salary,
avg(salary) OVER (PARTITION BY department) AS department_average
FROM employees;Each employee remains in the result, with the average for that employee’s department alongside the salary.
Rank employees within each department
SELECT department,
employee_id,
salary,
row_number() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id
) AS position
FROM employees;Numbering restarts for each department. Higher salaries come first, and the employee identifier resolves salary ties if it is unique. Without a tie-breaker, tied rows may receive row numbers in an unspecified order.
Filter on a calculated rank
In PostgreSQL, window functions can appear in the SELECT list and query-level ORDER BY, but not directly in WHERE, GROUP BY, or HAVING. Calculate the rank in an inner query, then filter its result in an outer query:
Recommended Free Tools
WITH ranked AS (
SELECT department,
employee_id,
salary,
row_number() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id
) AS position
FROM employees
)
SELECT department, employee_id, salary, position
FROM ranked
WHERE position <= 3;This returns up to three employee rows per department. The ranking is calculated before the outer query applies its filter.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Which rows can a window function see?
Window functions operate on the query’s virtual table after FROM, WHERE, GROUP BY, and HAVING have been applied. A row removed by an earlier WHERE condition cannot contribute to a window calculation. One SELECT can also contain multiple window functions with different OVER specifications, all working from that same virtual table. PostgreSQL explains this evaluation context in its window-functions tutorial.
PostgreSQL and SQL Server syntax
The examples above use PostgreSQL. SQL Server also supports an OVER clause in Transact-SQL, but details and supported options vary by database engine and version. Check the documentation for the engine and version you use rather than assuming every frame or function form transfers unchanged. Microsoft’s SQL Server 15 OVER clause reference documents its syntax.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
