ADVANCED

SQL Window Functions

Calculate rankings and running metrics without collapsing detail rows.

After this lessonUnderstand ROW_NUMBER, RANK and PARTITION BY.

How it works

Window functions perform calculations across related rows while keeping each original row visible. That makes them ideal for rankings, running totals and previous/next-row comparisons.

PARTITION BY restarts the calculation for each group. ORDER BY inside OVER defines the sequence used by the window function.

REAL WORK

Rank the latest transaction per PO, find the largest usage per material or calculate running production totals by shift.

SYNTAX

The pattern

ROW_NUMBER() OVER (
  PARTITION BY group_column
  ORDER BY value DESC
)

Example

SELECT OrderID, CustomerID, Quantity,
  ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Quantity DESC) AS rn
FROM Orders;

TRY IT

Run the example

Edit the query and run it against the built-in demo tables. Nothing is sent to a server.

SELECT CustomerID, Quantity
FROM Orders
ORDER BY CustomerID, Quantity DESC;

The learning runner supports SELECT, WHERE, LIKE, IN, BETWEEN, GROUP BY, HAVING, JOIN, aggregates, ORDER BY and LIMIT. Advanced lessons explain additional production SQL concepts even when the mini runner does not execute that syntax.

QUICK CHECK

Does GROUP BY keep every original detail row?

Progress is stored only in this browser. No account required.