PARTITION BY & ORDER BY in OVER

OVER() treats the whole table as one window. Most real questions are "per something": the total per player, the rank within each team, the running total per player by date. Two clauses inside OVER give you that control.

Definition

PARTITION BY divides the rows into independent groups called partitions. The window function is calculated separately inside each partition and restarts for the next one. Unlike GROUP BY, the rows are not collapsed.

ORDER BY (inside OVER) sorts the rows within each partition and tells the window function the order in which to process them. For aggregate functions it also changes the default frame, so the result becomes cumulative (a running total).

• Partition — like a GROUP BY group, but every row stays visible. Think of colour bands: all Kohli rows in one band, all Rohit rows in another.
• Restarts — Rohit's total does not include Kohli's runs. Each partition has its own calculation.
• ORDER BY inside OVER is different from the query's final ORDER BY: it only controls the calculation, not the display order of the result.

Syntax

window_function(expression) OVER (
    PARTITION BY column1 [, column2 ...]
    ORDER BY column3 [ASC | DESC] [, ...]
)

Example — PARTITION BY: a total per player

SELECT id, player, runs,
    SUM(runs) OVER (PARTITION BY player) AS player_total
FROM innings;

Step by step: (1) split the rows by player into 4 partitions; (2) SUM inside each partition; (3) write the partition's total on each of its rows. Each player gets their own total, and all 12 rows are kept.

Example — PARTITION BY + ORDER BY: running total per player

SELECT id, player, match_date, runs,
    SUM(runs) OVER (PARTITION BY player ORDER BY match_date) AS running
FROM innings;

Step by step: (1) partition by player; (2) sort each partition by date; (3) add up row by row — each row's value is "all runs so far". Dhoni's NULL innings adds nothing, so his running total stays 26. The last row of each partition equals the player's full total.

Why does ORDER BY make the total grow? When an aggregate window has ORDER BY but no frame, SQL uses the default frame from the first row of the partition up to the current row. Without ORDER BY the frame is the entire partition. Frames are explained in the next topic.

Common mistakes

• ❌ Thinking PARTITION BY reduces rows like GROUP BY → ✅ it only defines groups for the calculation; every row stays.
• ❌ Adding ORDER BY to OVER "just to make it neat" → ✅ for SUM/AVG/COUNT it changes the result from a total to a running total.
• ❌ Relying on OVER's ORDER BY to sort the output → ✅ add a final ORDER BY to the query if the display order matters.
• ❌ Running total ordered by a column with ties (two rows on the same date) → ✅ add a tie-breaker (ORDER BY match_date, id) and use a ROWS frame (next topic).

Interview tip

Q: What is the difference between PARTITION BY and GROUP BY? A: Both form groups, but GROUP BY returns one row per group, while PARTITION BY (inside OVER) keeps every row and only restarts the window calculation for each group. You can show each innings and its player's total side by side only with PARTITION BY.