SQL window functions calculate a value using related rows while keeping each input row in the result. Use OVER to define the rows a calculation can see, then choose a partition, ordering, and—when needed—a frame. That makes window functions useful for running totals, rankings, group totals, and previous- or next-row lookups without collapsing detail the way GROUP BY does.
What is a SQL window function?
A window function computes a result across a set of rows associated with the current row, then returns that result alongside the current row. For example, a sales query can show every sale and the salesperson’s total, or each employee and their department rank.
As an Amazon Associate I earn from qualifying purchases.
Window functions use an OVER clause. In PostgreSQL, an OVER clause is required for window-function syntax; Microsoft’s Transact-SQL documentation describes the clause in terms of partitioning, ordering, and framing. The precise syntax and available options vary by database.
Free tools Windows power users keep installed
One-click scans. No signup required.
Unlike a grouped aggregate such as SUM(amount) ... GROUP BY salesperson_id, a window aggregate can return one calculated value for each sale row. The detail rows remain visible; the calculation is attached to them.
#1 Best Overall
How to read the OVER clause
Read the clause as three questions: which rows belong together, what order applies, and which subset contributes to this row’s result?
PARTITION BYdefines calculation groups. It does not reduce the result to one row per group.ORDER BYdefines the order used by ranking and ordered calculations. It does not necessarily sort the final output.- A frame clause, when specified, narrows the rows within the partition that contribute to the calculation for a given row.
This example uses PostgreSQL-style SQL syntax as an illustration; verify it against the database and version you use:
SELECT
salesperson_id,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY salesperson_id
ORDER BY sale_date, sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_sales
FROM sales;The partition starts a separate running total for each salesperson. The frame includes rows from the start of that salesperson’s ordered partition through the current row. Including sale_id as a tie-breaker makes the intended row-by-row sequence explicit when multiple sales share a date; an ordering that leaves ties unresolved may not specify a unique sequence.
The ORDER BY inside OVER controls the calculation, not necessarily display order. Add an outer ORDER BY if the returned rows must appear in a particular sequence.
How to calculate a running total or a repeated group total
Running total
For an accumulating total, order the rows and use a frame from the partition start through the current row. The example above spells out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so each row adds its amount to earlier rows in the defined order.
Total repeated on each row
If every sale should display its salesperson’s full total, use an aggregate window without ordering:
SUM(amount) OVER (PARTITION BY salesperson_id) AS salesperson_totalWith no ordering needed for this group-wide calculation, the aggregate covers the partition. If an ordered window is needed for another reason, use an explicit full-partition frame when supported, such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →How ROW_NUMBER, RANK, and DENSE_RANK handle ties
Ranking functions use the ordering in OVER. Rows with the same ordering value are peers; RANK and DENSE_RANK give peers the same rank, while ROW_NUMBER assigns each row a distinct number.
| Function | Result for tied ordering values | What happens after a tie |
|---|---|---|
ROW_NUMBER |
Each row gets a distinct sequence number. | No rank gap; the sequence continues, though tied rows need a tie-breaker if their individual order matters. |
RANK |
Peers get the same rank. | Leaves gaps. If two rows tie at rank 1, the next rank is 3. |
DENSE_RANK |
Peers get the same rank. | No gaps. If two rows tie at rank 1, the next rank is 2. |
For a top-N-per-group query, filtering to ROW_NUMBER() <= N returns N rows per partition when the ordering is unique and each partition has at least N rows. If you rank by a value that can tie, RANK() <= N can return more than N rows because it preserves ties. Choose the function according to whether the limit means a fixed row count or a rank cutoff that includes peers.
How LAG, LEAD, and value functions work
Value window functions let a row refer to values at other positions in its ordered partition. LAG looks to an earlier row and LEAD to a later row; they are useful for comparing a measurement with the previous or next event without joining the table to itself. Functions such as FIRST_VALUE, LAST_VALUE, and NTH_VALUE select values from rows in the current frame.
Ordering matters: it determines which row counts as previous, next, first, or last. If that sequence must be deterministic among tied values, include an appropriate tie-breaker. Null-handling options and advanced syntax are not uniform across engines. PostgreSQL 18 documents that its LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTH_VALUE always respect nulls; it does not implement IGNORE NULLS or FROM LAST for NTH_VALUE. See the PostgreSQL window-functions documentation.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsWhat a window frame does—and why the default can surprise you
A frame is the subset of the ordered partition used for the current row’s calculation. PostgreSQL documents that, when ORDER BY is present and no frame is written, the default frame runs from the partition start through the current row and its last peer. SQLite describes the default aggregate frame similarly. As a result, an ordered aggregate commonly behaves like a running total, including the current row’s peers, rather than a total of the entire partition.
That behavior is useful when you want a cumulative calculation, but may be wrong when you expect every row to show the same full-partition value. Omit ORDER BY if the calculation does not need an order, or specify a full-partition frame, subject to the engine’s syntax and function support.
ROWS, RANGE, and GROUPS
ROWScounts physical rows relative to the current row. It suits row-by-row frames such as a running total with a defined sequence.RANGEworks with ordering values and peer relationships. Its allowed offsets and exact behavior depend on the database.GROUPScounts peer groups—sets of rows tied on the window ordering—where the database supports it.
Peer behavior is especially important for RANGE and GROUPS: tied ordering values may be included together rather than treated as separate positions. PostgreSQL and SQLite document all three frame types, while Microsoft’s Transact-SQL OVER documentation describes ROWS and RANGE. Do not assume identical support across products; check the documentation for your engine and version before using offsets, exclusions, or other advanced frame syntax.
Why LAST_VALUE may not return the partition’s last value
LAST_VALUE reads from the current frame, not automatically from the end of the entire partition. With an ordered default frame that ends at the current row and its peers, it may return a value from that frame rather than the final value in the partition. If you want the partition’s final value on every row, define a frame that reaches the partition end, for example ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, where supported.
Check your database’s syntax and version
Window-function features differ by dialect and version. The examples here use familiar SQL syntax, but they are not claimed to have been tested on every engine. PostgreSQL 18, SQLite’s official documentation, and Microsoft Learn’s Transact-SQL documentation describe overlapping but non-identical feature sets. Before relying on a frame type, offset, exclusion, null-handling option, or value-function behavior, check the documentation for your exact database version:
Quick Recap
- PostgreSQL 18: Window Functions
- SQLite: Window Functions
- Microsoft Learn: OVER Clause (Transact-SQL), view version 17
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.
