What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
SQL window functions calculate across related rows without collapsing those rows into one result. Add an OVER clause to a supported aggregate or function, use PARTITION BY for independent groups, and use the window’s ORDER BY to define calculation order. This guide shows running totals, rankings, top-N queries, previous-row comparisons, frame behavior, and dialect caveats for PostgreSQL, SQLite, SQL Server, and MySQL.
What a window function does
A grouped aggregate such as SUM(amount) GROUP BY customer_id returns one row per customer. The windowed form, SUM(amount) OVER (PARTITION BY customer_id), keeps every order row and adds the customer’s total beside it.
The general form is:
function_name(arguments) OVER (
PARTITION BY grouping_column
ORDER BY sort_column, unique_tie_breaker
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)- PARTITION BY divides the input into independent groups. Without it, all rows are one partition.
- ORDER BY inside
OVERcontrols calculation order. It does not guarantee the final display order; add an outerORDER BYwhen presentation order matters. - Frame limits which rows around the current row an aggregate can see. Ranking functions generally use the partition ordering but do not accept a frame clause.
Window expressions are evaluated after filtering and grouping at the same query level in common SQL implementations. Consequently, compute a window value in a CTE or subquery before filtering it in an outer query.
Running totals and moving calculations
Running total per customer
SELECT
customer_id,
order_date,
order_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer_id, order_date, order_id;The explicit ROWS frame accumulates one physical row at a time. order_id is a unique tie-breaker for orders sharing a date, making the result deterministic. The same pattern works with AVG for a cumulative average or another supported aggregate.
Recommended Free Tools
#1 Best Overall
Full-partition total on every row
SELECT
customer_id,
order_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;Because there is no window ordering, each row sees the whole customer partition. Use this when you want a denominator for percentages or a repeated group total rather than a cumulative value.
Moving average
SELECT
account_id,
transaction_date,
amount,
AVG(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_row_average
FROM transactions;This is a seven-row average, not necessarily a seven-calendar-day average. A calendar interval requires date-aware syntax that differs by engine; verify the target dialect before changing the frame.
Ranking rows within groups
SELECT
department_id,
employee_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS row_num,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dense_salary_rank
FROM employees;| Function | Ties | What happens after a tie | Typical use |
|---|---|---|---|
ROW_NUMBER() |
Never shares a number | No gaps, but tie order is arbitrary unless you add a unique key | Select exactly one row or assign a sequence |
RANK() |
Shares rank | Leaves gaps (1, 1, 3) | Competition-style ranking |
DENSE_RANK() |
Shares rank | No gaps (1, 1, 2) | Distinct salary or score positions |
Rows equal on every expression in the window ORDER BY are peers. Add a unique tie-breaker to ROW_NUMBER when repeatable output is required; omit it for RANK or DENSE_RANK when equal values should remain tied.
Top N rows per group
Window results normally cannot be referenced directly in WHERE at the same query level. Rank first, then filter:
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
ORDER BY department_id, rn;Use ROW_NUMBER for exactly three employees per department. Use RANK if everyone tied at the third salary should be included, accepting that a department can return more than three rows.
Previous and next values
Compare with the previous transaction
SELECT
account_id,
transaction_date,
transaction_id,
amount,
LAG(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
) AS previous_amount
FROM transactions;The first row in each account has no predecessor and normally returns NULL. Many engines support an offset and default argument, but their exact syntax and type rules vary. LEAD applies the same idea to the next row.
Calculate a change
WITH values AS (
SELECT
account_id,
transaction_date,
amount,
LAG(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
) AS previous_amount
FROM transactions
)
SELECT
account_id,
transaction_date,
amount,
amount - previous_amount AS change_from_previous
FROM values;ROWS versus RANGE versus GROUPS
This is the most common source of surprising cumulative results. A frame describes boundaries relative to the current row:
| Frame type | Boundary counts | Important consequence |
|---|---|---|
ROWS |
Individual physical rows | With a unique ordering, a running aggregate advances one row at a time. |
RANGE |
Ordering values and their peers | Equal ordering values can share one frame and one cumulative result. |
GROUPS |
Peer groups | Boundaries move by sets of equal ordering values. |
With an ordered aggregate, the default is commonly equivalent to a range from the partition start through the current row and its peers. Suppose two orders have the same order_date. A default cumulative SUM may give both rows the same total, including both same-date orders. For literal row-by-row behavior, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and include a unique tie-breaker.
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 problemsFrame support and boundary syntax are dialect-specific. SQLite documents all three frame families and peer behavior; PostgreSQL documents the ordered default frame and its effect on aggregates. Check your engine’s version reference before using GROUPS, exclusions, value-based ranges, or interval boundaries.
Window placement, named windows, and portability
Where expressions are legal
PostgreSQL permits window functions in the SELECT list and query ORDER BY. To filter, join, or reuse a calculated rank, put the statement in a CTE or subquery. Other systems have similar logical restrictions, but confirm the dialect rather than relying on a vendor extension.
Named windows
Some engines let you define a window once and reference it repeatedly:
SELECT
employee_id,
department_id,
salary,
RANK() OVER w AS salary_rank,
DENSE_RANK() OVER w AS dense_salary_rank
FROM employees
WINDOW w AS (
PARTITION BY department_id
ORDER BY salary DESC
);Named-window syntax is documented in PostgreSQL and SQLite. Microsoft documents a WINDOW clause for SQL Server 2022 (16.x) and later, including Azure SQL and Fabric contexts. Do not assume an older SQL Server release accepts it.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Version checklist
- PostgreSQL 18: documentation covers partitions, ordering, default frames, filtering through subqueries, and named windows.
- SQLite: documentation covers aggregate and built-in ranking/value functions, peers,
ROWS/GROUPS/RANGE, and named windows. - SQL Server: consult the SQL Server 2022 (16.x) and later
WINDOWandOVERreferences; ranking functions do not take frame clauses. - MySQL 8.4: check the version-specific
OVER, aggregate-window, and value-function references before using less-portable options.
The examples here are illustrative patterns, not claims that they were executed on every listed engine. Test against your engine and schema, especially for null handling, frame boundaries, date arithmetic, and named-window syntax.
Compact window-function cheat sheet
| Need | Pattern | Check before shipping |
|---|---|---|
| Number ordered rows | ROW_NUMBER() OVER (...) |
Add a deterministic tie-breaker. |
| Rank with gaps | RANK() OVER (...) |
Peers share a rank. |
| Rank without gaps | DENSE_RANK() OVER (...) |
Confirm support in the target engine. |
| Running sum or average | SUM(x) OVER (...), AVG(x) OVER (...) |
Specify a ROWS frame for row-by-row accumulation. |
| Previous or next value | LAG(x) OVER (...), LEAD(x) OVER (...) |
Check offset and default-argument syntax. |
| First or last value | FIRST_VALUE(x), LAST_VALUE(x) |
Frame bounds determine which value is visible. |
| Filter top N | CTE/subquery, then outer WHERE |
Do not filter the window alias at the same query stage. |
Performance and correctness checks
- Index or cluster data on partition and ordering columns where your engine can use those structures; inspect the execution plan rather than assuming an index removes sorting.
- Keep the window ordering as narrow as the business rule allows, but include a unique key when deterministic row order matters.
- Use one window specification for calculations that share partitioning and ordering; separate specifications can require separate sorts.
- Decide how
NULLvalues should rank and whether they belong in totals. Null ordering and aggregate behavior vary by function and dialect. - Use an outer
ORDER BYfor presentation. The order insideOVERalone is not a promise about returned row order.
Common errors and fixes
“Window function is not allowed in WHERE”
Compute it in a CTE or subquery and filter in the outer query, as in the top-three example.
Unexpected jumps in a running total
Equal ordering values are peers under the default range frame. Add a unique ordering key and an explicit ROWS frame.
Top-N output changes between runs
ROW_NUMBER has no defined tie order when its ordering expressions are equal. Append a stable primary key.
Free tools Windows power users keep installed
One-click scans. No signup required.
“Function or frame syntax not recognized”
Check the exact database product and version. GROUPS, named windows, value-based RANGE boundaries, and offset defaults are not uniformly available.
Last value appears to be the current value
LAST_VALUE is frame-sensitive. An ending boundary at the current row makes the current row the last visible value; define a frame ending at UNBOUNDED FOLLOWING when that is the intended result, subject to your dialect’s syntax.
Or skip the browser setup
If you need screenshots of SQL result pages, documentation, or dashboards while preparing technical material, ScreenshotNeo provides a single-call API. It accepts a URL and can return PNG, JPEG, WebP, or PDF. Before capture it accepts cookie or consent banners and removes more than 60 known consent platforms, newsletter popups, and chat widgets; each cleanup step can be disabled. Bot checks or CAPTCHAs, blank pages, timeouts, failed loads, and cache hits are not billed, and response headers identify the page verdict and billing status.
cURL (see the ScreenshotNeo API documentation):
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpPython:
import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)Node.js:
const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);ScreenshotNeo also offers an MCP server with take_screenshot, get_page_info, and capture_pdf for Claude, Cursor, and other MCP clients. It supports full-page and element captures, device presets, dark mode, custom CSS or JavaScript, waits, request blocking, headers and cookies, geolocation, resizing, caching, signed links, asynchronous webhooks, bulk capture of up to 100 URLs per call, and a usage API. The Free plan includes 1,000 screenshots per month without a card; paid plans start at $5 for 3,000 shots, and every feature is available on every plan. Create a free ScreenshotNeo account.
FAQ
Can a window function replace GROUP BY?
Not generally. GROUP BY reduces rows; a window function preserves them. Choose based on whether detail rows must remain visible.
Can I use multiple window functions in one SELECT?
Yes. Give each expression its own OVER clause or use a named window where your engine supports it.
Why does adding ORDER BY change an aggregate window result?
Adding ordering introduces a frame, commonly through the current row and its peers, so the aggregate becomes cumulative instead of a repeated partition total.
Frequently Asked Questions
Can a window function replace GROUP BY?
Not generally. GROUP BY reduces rows; a window function preserves them. Choose based on whether detail rows must remain visible.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Can I use multiple window functions in one SELECT?
Yes. Give each expression its own OVER clause or use a named window where your engine supports it.
Why does adding ORDER BY change an aggregate window result?
Adding ordering introduces a frame, commonly through the current row and its peers, so the aggregate becomes cumulative instead of a repeated partition total.
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.
