SQL Window Functions

SQL window functions calculate values across a set of rows related to the current row, without collapsing them into one row like GROUP BY does. They make rankings, running totals and "compare with previous" queries easy. They are supported in MySQL 8+, PostgreSQL, SQL Server, Oracle and SQLite 3.25+.

ROW_NUMBER

Number the products from most to least expensive. OVER (...) defines the window.

SELECT ROW_NUMBER() OVER (ORDER BY Price DESC) AS rn,
       ProductName, Price
FROM Products;

rn | ProductName    | Price
---+----------------+------
1  | Coffee Beans   | 22
2  | Olive Oil      | 18.75
3  | Almonds        | 15
4  | Basmati Rice   | 12.5
5  | Honey          | 11.5
6  | Wheat Flour    | 8
7  | Green Tea      | 6
8  | Dark Chocolate | 4.25
(8 rows)

PARTITION BY

PARTITION BY restarts the calculation for each group. Here products are ranked within their own category.

SELECT Category, ProductName, Price,
       RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS rank_in_cat
FROM Products
ORDER BY Category, rank_in_cat;

Category   | ProductName    | Price | rank_in_cat
-----------+----------------+-------+------------
Beverages  | Coffee Beans   | 22    | 1
Beverages  | Green Tea      | 6     | 2
Grains     | Basmati Rice   | 12.5  | 1
Grains     | Wheat Flour    | 8     | 2
Oils       | Olive Oil      | 18.75 | 1
Snacks     | Almonds        | 15    | 1
Snacks     | Dark Chocolate | 4.25  | 2
Sweeteners | Honey          | 11.5  | 1
(8 rows)

Running Total

SUM() OVER with ORDER BY gives a running total of units ordered over time.

SELECT OrderID, OrderDate, Quantity,
       SUM(Quantity) OVER (ORDER BY OrderDate) AS running_units
FROM Orders;

OrderID | OrderDate  | Quantity | running_units
--------+------------+----------+--------------
101     | 2026-01-05 | 10       | 10
102     | 2026-01-09 | 25       | 35
103     | 2026-02-11 | 4        | 39
104     | 2026-02-14 | 6        | 45
105     | 2026-03-02 | 40       | 85
106     | 2026-03-20 | 12       | 97
107     | 2026-04-01 | 8        | 105
108     | 2026-04-18 | 30       | 135
109     | 2026-05-07 | 15       | 150
110     | 2026-05-22 | 5        | 155
(10 rows)

LAG: Compare with the Previous Row

LAG returns a value from the previous row in the window, perfect for "change since last time".

SELECT OrderID, Quantity,
       LAG(Quantity) OVER (ORDER BY OrderID) AS prev_qty
FROM Orders
LIMIT 4;

OrderID | Quantity | prev_qty
--------+----------+---------
101     | 10       | NULL
102     | 25       | 10
103     | 4        | 25
104     | 6        | 4
(4 rows)

Frequently asked questions

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 1, 3) while DENSE_RANK does not (1, 1, 2). ROW_NUMBER never gives ties.

How is a window function different from GROUP BY?

GROUP BY returns one row per group. A window function keeps every row and adds the calculated value next to it.

Can I filter on a window function in WHERE?

No, window functions are computed after WHERE. Put the query in a subquery or CTE and filter in the outer query.