How do window functions differ from aggregates?

An aggregate with GROUP BY collapses each group to one row. A window function computes a value across a set of related rows but keeps every input row in the output. The window is defined by the OVER() clause rather than GROUP BY.

What goes inside OVER()?

OVER() can contain PARTITION BY to split rows into independent groups, ORDER BY to order rows within each partition, and a frame clause (ROWS or RANGE) to bound which rows the function sees. Ranking and offset functions require ORDER BY; plain aggregates do not.

What is the difference between RANK and DENSE_RANK?

Both assign the same rank to tied rows, but RANK leaves gaps afterwards (1, 1, 3) while DENSE_RANK does not (1, 1, 2). ROW_NUMBER ignores ties entirely and assigns a unique sequential number to every row in order.

What does the frame clause control?

The frame (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) limits which rows an aggregate or value function sees within the ordered partition. It is how you build running totals and moving averages. ROWS counts physical rows; RANGE groups by ordering-value peers.

Does this tool send my query anywhere?

No. The function list is bundled into the page and filtered entirely in your browser. Nothing is uploaded, logged or stored.

SQL Window Functions Reference

Email me this result

Get this tool's output sent to your inbox, plus one useful tool a week. No spam, unsubscribe any time.

What window functions do

A window function performs a calculation across a set of rows related to the current row — a ranking, a running total, the previous row’s value — without collapsing the result the way GROUP BY does. Every input row stays in the output. This reference lists the ranking, offset and value functions plus the OVER() clause they all share, with per-database support.

How it works

The OVER() clause defines the window: how rows are partitioned, ordered, and framed:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       LAG(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS prev,
       SUM(salary)  OVER (PARTITION BY department
                          ORDER BY salary
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM   employees;

PARTITION BY resets the calculation per group, ORDER BY orders rows within each partition, and the frame clause bounds the rows an aggregate or value function sees. Ranking and offset functions require ORDER BY. The tool filters the table as you type and flags dialect support.

Tips and notes

Window functions run after WHERE/GROUP BY — you cannot filter on them in WHERE; use a subquery or QUALIFY (where supported).
ROWS frames count physical rows; RANGE frames group peer rows with equal ORDER BY values.
The default frame for an ordered window is RANGE UNBOUNDED PRECEDING TO CURRENT ROW.
LAG/LEAD take optional offset and default arguments: LAG(x, 2, 0).