Free tools Windows power users keep installed
One-click scans. No signup required.
SQL window functions calculate values across related rows while keeping each input row in the result. Use GROUP BY when you want one output row per group; use a window function when you want a group calculation—such as a total or rank—alongside the detail rows.
Window function versus GROUP BY: the difference in the result
Consider a transactions table with account_id, posted_at, transaction_id, and amount. Suppose account 7 has two transactions of 20 and 35, while account 9 has one transaction of 12.
As an Amazon Associate I earn from qualifying purchases.
A grouped total returns one row per account:
SELECT account_id, SUM(amount) AS account_total
FROM transactions
GROUP BY account_id;The result has two rows: account 7 with 55, and account 9 with 12. A windowed total keeps the transaction rows and adds the relevant total to each:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
SELECT
account_id,
transaction_id,
amount,
SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions;This result has three rows, with 55 beside each transaction for account 7 and 12 beside the transaction for account 9. A window function calculates for each row from a related set of rows; it does not, by itself, collapse those rows. PostgreSQL describes the calculation as operating across rows related to the current row (PostgreSQL 18 window tutorial).
#1 Best Overall
How OVER, PARTITION BY, and ORDER BY work
OVER marks a function as a window calculation and defines the rows it can use. Its clauses answer different questions:
PARTITION BY account_idsplits rows into account-sized calculation groups. The function restarts for each account. WithoutPARTITION BY, the query’s rows form one partition.ORDER BY posted_at, transaction_iddefines sequence and peer relationships inside the window. The transaction ID provides a deterministic tie-breaker when timestamps match.- The query-level
ORDER BYcontrols how the final result is displayed; it does not replace the ordering insideOVER.
For example, this PostgreSQL-compatible query creates a row-by-row running total per account:
SELECT
account_id,
posted_at,
transaction_id,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY posted_at, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM transactions
ORDER BY account_id, posted_at, transaction_id;The partition restarts the calculation for each account. The window ordering establishes the transaction sequence, and the explicit ROWS frame includes physical rows from the beginning of that account’s partition through the current transaction. This is an illustrative query, not a reported execution test.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsRanking rows, including ties
Use ranking functions when each row needs a position or rank within its partition. Given scores 100, 90, 90, and 80, their tie behavior differs:
| Function | Ranks for scores 100, 90, 90, 80 | What happens at ties |
|---|---|---|
ROW_NUMBER() |
1, 2, 3, 4 | Every row gets a distinct number. The order between tied rows is not predictable unless the window ordering includes a tie-breaker. |
RANK() |
1, 2, 2, 4 | Tied rows share a rank; later ranks leave gaps. |
DENSE_RANK() |
1, 2, 2, 3 | Tied rows share a rank; later ranks have no gaps. |
Example using a score per player within each contest:
SELECT
contest_id,
player_id,
score,
ROW_NUMBER() OVER (
PARTITION BY contest_id
ORDER BY score DESC, player_id
) AS row_num,
RANK() OVER (
PARTITION BY contest_id
ORDER BY score DESC
) AS score_rank,
DENSE_RANK() OVER (
PARTITION BY contest_id
ORDER BY score DESC
) AS dense_score_rank
FROM results;For exactly three rows per department, use ROW_NUMBER() with a deterministic ordering and filter it in an outer query. If everyone tied at the cutoff should remain, use RANK() or DENSE_RANK() according to whether gaps in rank numbers matter. Ranking values do not guarantee a fixed number of returned rows when ties are retained.
Filter on a window result with a CTE or subquery
In PostgreSQL, window calculations occur after WHERE, GROUP BY, and HAVING. That means a window result cannot be referenced directly in the same query’s WHERE clause. Calculate it in an inner query, then filter from the outer query:
WITH ranked AS (
SELECT
department_id,
employee_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rn <= 3;Here, employee_id resolves equal-salary ordering, and the outer filter returns at most three employees per department. To include all employees tied for third, use a ranking function instead and choose the tie behavior you want.
Frames: control which partition rows a calculation sees
A window frame is the subset of the current partition that a frame-sensitive function uses for a particular row. It is especially important for aggregate windows and value functions. With an ordered aggregate, common database defaults extend from the partition start through the current row and its peers—rows tied on the window’s ordering values. This is why an apparently row-by-row running total can give tied rows the same cumulative result.
Choose ROWS for physical row boundaries
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes individual row positions from the partition start through the current row. Use it for a row-by-row running total, and provide enough ordering columns to make the sequence deterministic when that matters.
Understand RANGE and peers
RANGE uses logical relationships to the ordering values rather than simply counting physical row positions. Rows with the same ordering value can be peers and share a frame boundary. SQL Server’s OVER reference distinguishes physical ROWS boundaries from logical RANGE boundaries (Microsoft Learn: SELECT – OVER clause). Do not substitute one for the other without checking whether peer behavior is intended.
Use the whole partition for a repeated total
To show the same full-partition total beside every row, omit window ordering for an aggregate such as SUM(amount) OVER (PARTITION BY account_id), or specify a full-partition frame using syntax supported by your database. Adding ORDER BY without an explicit full frame can change the calculation into a cumulative one.
Best Value
Compare rows with LAG and LEAD
LAG reads an expression from a preceding row in the ordered partition; LEAD reads from a following row. For example, this PostgreSQL-compatible expression calculates the change from the prior transaction:
SELECT
account_id,
posted_at,
transaction_id,
amount,
amount - LAG(amount) OVER (
PARTITION BY account_id
ORDER BY posted_at, transaction_id
) AS change_from_previous
FROM transactions;The first row in each account has no preceding row, so the result is NULL unless you supply a default. SQLite documents that an offset beyond the partition returns NULL unless a default is provided (SQLite window functions). Check your engine’s function signature when supplying optional offset or default arguments.
FIRST_VALUE, LAST_VALUE, and NTH_VALUE depend on the frame
FIRST_VALUE, LAST_VALUE, and NTH_VALUE return values from rows in the active frame. With an ordered window’s common default frame, LAST_VALUE often means the last value through the current row and its peers—not the final value in the entire partition. If you want each row to show the partition’s final value, extend the frame through the partition end, using syntax supported by the target database:
LAST_VALUE(amount) OVER (
PARTITION BY account_id
ORDER BY posted_at, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)PostgreSQL’s documentation warns that the default frame can make LAST_VALUE and sometimes NTH_VALUE unhelpful for the intended purpose (PostgreSQL 18 window functions). The same reference states that PostgreSQL’s lead, lag, and value functions always use RESPECT NULLS; PostgreSQL does not implement the SQL-standard IGNORE NULLS option.
Check syntax and behavior for your database
The concepts are shared across major SQL engines, but frame syntax, defaults, function support, and null handling are not interchangeable. Verify the database and version you are using before moving a query between systems.
| Database documentation | Specific detail documented |
|---|---|
| PostgreSQL 18 tutorial | Window functions operate on the query’s virtual table after filtering and grouping clauses; default ordered-frame peer behavior is explained. |
| PostgreSQL 18 function reference | Documents ranking, navigation, and value functions, including PostgreSQL’s RESPECT NULLS behavior. |
| SQL Server OVER reference | Documents ROWS and RANGE frame behavior; its frame support applies to SQL Server 2012 and later. |
| SQLite window-function reference | Documents ROWS, GROUPS, and RANGE, the default aggregate frame, and which built-in functions honor frames. |
| MySQL 8.0 window-function usage | Documents row-numbering behavior and notes that numbering rows without an ordering is nondeterministic. |
When adapting a query, check the supported functions, whether the function observes a frame, the default frame and peer behavior, null-handling options, and engine-version restrictions. An explicit frame and ordering make intent clearer, but the exact accepted forms still depend on the database.
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.
