ORDER BY in Window
SQL · Window Functions
Theory
ORDER BY inside OVER() sets the order in which rows are walked through within each partition. It is separate from the ORDER BY at the end of the query: the window ORDER BY decides how the calculation runs, the final ORDER BY decides how the result is displayed. They can be different.
This ordering is what makes ranking (ROW_NUMBER, RANK), offsets (LAG, LEAD) and running totals meaningful. It also quietly changes how aggregates behave. SUM(revenue) OVER () gives the grand total on every row, but SUM(revenue) OVER (ORDER BY month) gives a running total, because adding ORDER BY switches the default frame to 'from the first row up to the current row'. If the ORDER BY column has ties, tied rows are treated as one step in that default frame, so add a tie-breaker column when you need a strict row-by-row order.
Why we use it
Many business questions depend on sequence: month-on-month growth, rank by score, cumulative sales so far. Without an ordering inside the window, the database has no notion of 'previous', 'first' or 'so far'.
When to use it
Use ORDER BY in OVER for any ranking, LAG/LEAD, running total or moving average. Leave it out when you want a plain group total on every row. Always include a unique tie-breaker (like id) when the result must be deterministic.
How it works
- Rows are split into partitions
- Rows in each partition are sorted by the window ORDER BY
- Aggregate like SUM/AVG? Yes: Running frame: start to current. No: Rank/offset uses the order.
- Compute the value for each row in sorted order
- Final ORDER BY sorts the output for display
- Result returned
Syntax
function() OVER (ORDER BY column)Example
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;Key points
- Required for ROW_NUMBER, RANK, LAG, LEAD to be meaningful
- Can combine with PARTITION BY: order restarts per partition
- Window ORDER BY is separate from the query's final ORDER BY
- With an aggregate, it creates a running total by default
- Add a tie-breaker column for deterministic results