SQL Window Functions: ROW_NUMBER, RANK, LAG and Running Totals

SQL Tutorial · Chapter 30 of 32

SQL window functions perform a calculation across a set of rows related to the current row, without collapsing those rows the way GROUP BY does. Each row keeps its own detail and gains an extra column: a rank, a running total, the previous row’s value or a group total. The OVER clause defines the “window” of rows the function looks at.

SQL Window Function Syntax

function_name(arguments) OVER (
    [PARTITION BY column]   -- restart the calculation for each group
    [ORDER BY column]       -- order rows inside the window
)

Sample Table

Example 1: ROW_NUMBER

SELECT OrderID, Amount,
       ROW_NUMBER() OVER (ORDER BY Amount DESC) AS RowNum
FROM Orders;

All six rows survive. Compare with GROUP BY, which would have returned one row per group.

Example 2: RANK vs DENSE_RANK with Ties

Ranking orders by month creates ties: three orders share January.

SELECT OrderID, SUBSTR(OrderDate, 1, 7) AS Month,
       RANK()       OVER (ORDER BY SUBSTR(OrderDate, 1, 7)) AS Rnk,
       DENSE_RANK() OVER (ORDER BY SUBSTR(OrderDate, 1, 7)) AS DenseRnk
FROM Orders;

RANK skips to 4 after three tied rows; DENSE_RANK continues with 2.

Example 3: Running Total with SUM OVER

SELECT OrderID, OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

With ORDER BY inside OVER, the default frame is “from the first row up to the current row”, which is exactly a running total.

Example 4: PARTITION BY for Group Totals on Every Row

SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotal,
       ROUND(100.0 * Amount / SUM(Amount) OVER (PARTITION BY CustomerID), 1) AS PctOfCustomer
FROM Orders
ORDER BY CustomerID, OrderID;

Example 5: LAG to Compare with the Previous Row

SELECT OrderID, OrderDate, Amount,
       LAG(Amount) OVER (ORDER BY OrderDate) AS PrevAmount,
       Amount - LAG(Amount) OVER (ORDER BY OrderDate) AS Change
FROM Orders;

LEAD works the same way but looks at the next row. The first row has no predecessor, so LAG returns NULL.

Example 6: Top-N per Group

Window functions cannot appear in WHERE, so wrap the query in a subquery or CTE and filter on the result. This returns each customer’s largest order.

WITH ranked AS (
  SELECT OrderID, CustomerID, Amount,
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rn
  FROM Orders
)
SELECT OrderID, CustomerID, Amount
FROM ranked
WHERE rn = 1
ORDER BY CustomerID;

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Window functions are supported in MySQL 8.0+, SQL Server 2012+, PostgreSQL 8.4+ and SQLite 3.25+. MySQL 5.7 and older do not have them.
• Month extraction differs: SQL Server uses FORMAT(OrderDate, 'yyyy-MM'), MySQL DATE_FORMAT, PostgreSQL TO_CHAR; the window syntax itself is identical.
• All four support the WINDOW clause to name a window once and reuse it, except SQL Server.

Common Mistakes

• Putting a window function in WHERE or HAVING; filter through a subquery or CTE instead.
• Forgetting ORDER BY inside OVER for ROW_NUMBER, RANK, LAG or running totals, which makes the result arbitrary.
• Confusing RANK and DENSE_RANK when gaps matter for reporting.
• Expecting SUM() OVER (ORDER BY ...) to give a grand total; without ORDER BY it does, with ORDER BY it is cumulative.

Practice Exercise

1. Number each customer’s orders from oldest to newest.
2. Show each order with the average Amount of all orders on the same row.

-- 1
SELECT CustomerID, OrderID, OrderDate,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderSeq
FROM Orders;
-- customer 1: 101 = 1, 103 = 2; all others = 1

-- 2
SELECT OrderID, Amount, AVG(Amount) OVER () AS OverallAvg FROM Orders;
-- OverallAvg = 466.67 on every row

Related Chapters

• SQL GROUP BY – aggregation that collapses rows
• SQL TOP / LIMIT – simple top-N without groups
• SQL Subqueries – the wrapper you need to filter on a window column
• SQL from Scratch: full course overview

FAQ

What is the difference between a window function and GROUP BY?

GROUP BY merges rows into one row per group. A window function keeps every row and adds the aggregate, rank or offset value as a new column.

What does PARTITION BY do?

It splits the rows into groups so the window function restarts for each group, like GROUP BY but without collapsing the rows.

Can I use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE. Put the query in a subquery or CTE and filter on the window column in the outer query.

Chapter 30 of 32 · SQL from Scratch: all 32 chapters