What is the default window frame when OVER includes ORDER BY, and when it omits it?

## The three parts of OVER A window specification has up to three parts: `PARTITION BY` splits rows into independent groups, `ORDER BY` orders the rows inside each partition, and the **frame clause** selects which slice of that ordered partition feeds the function for the row currently being computed. The frame is recomputed for every row. When you omit the frame clause, you do not get "no frame" — you get a default one, and which default you get depends on whether the window has an `ORDER BY`. ## Default with ORDER BY If the window has an `ORDER BY` and no frame clause, the default is: ```sql RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ``` Read it carefully, because the surprise is hidden inside `CURRENT ROW`. In **RANGE** mode the bounds are expressed in terms of the *ordering value*, not the row position. Rows that share the same `ORDER BY` value are called **peers**. `CURRENT ROW` as a RANGE end bound means "through the last peer of the current row". So the default frame is: everything from the start of the partition up to and including *all rows tied with me*. That produces the classic tied-value surprise: ```sql -- scores: 50, 70, 70, 90 (one row each) SELECT score, SUM(score) OVER (ORDER BY score) AS default_frame FROM exam_scores; -- 50 -> 50 -- 70 -> 190 (50 + 70 + 70: both peers included) -- 70 -> 190 (the same value repeated) -- 90 -> 280 ``` A reader expecting 50, 120, 190, 280 sees the second value "skipped". Nothing is broken; the default frame simply refuses to split a peer group. ## Default without ORDER BY With no `ORDER BY` in the window, there is no ordering, so "rows preceding me" is undefined. The frame is then the **entire partition** — equivalent to `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`. This is exactly why `SUM(amount) OVER (PARTITION BY dept_id)` gives every row of a department the same department total: that is the whole-partition frame at work, and it is the standard way to compute a percent-of-total denominator alongside detail rows. So adding `ORDER BY` to an `OVER` clause does not merely sort — it silently changes the frame from "whole partition" to "start of partition through my peers", which changes the number the function returns. Candidates who think `ORDER BY` inside `OVER` is cosmetic get this wrong. ## Getting the row-by-row behaviour you meant Write the frame explicitly: ```sql SUM(score) OVER (ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 50, 120, 190, 280 ``` In **ROWS** mode `CURRENT ROW` means this physical row and nothing else, so each tied row gets its own accumulating value. The abbreviated single-bound form `ROWS UNBOUNDED PRECEDING` means the same thing — when only a start bound is given, the end bound is `CURRENT ROW`. A useful habit: if the ordering key is unique (a primary key, a strictly increasing timestamp), the default frame and `ROWS ... CURRENT ROW` agree, so the default is harmless. The moment the ordering key can repeat — a date column on a table with several rows per day, a score, a category — decide deliberately which behaviour you want and spell it out. ## Which behaviour is actually right? Neither default is a bug; both answer a real question. - **Peer-inclusive (RANGE default)** is what you want when tied rows must agree: every row dated 2024-01-02 showing the same end-of-day cumulative total is a legitimate, often desirable report. - **Row-by-row (ROWS)** is what you want when each row must show its own position in the accumulation, or when you need a deterministic per-row answer. ## Frames without an ORDER BY, and functions that ignore frames Some engines accept an explicit frame with no window `ORDER BY`; the result is unspecified because row order inside the partition is unspecified, so it is not a useful thing to write. `RANGE` with a numeric or interval offset, and `GROUPS` mode, require an `ORDER BY` outright. Finally, the frame does not affect every window function. Ranking functions (`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `PERCENT_RANK`, `CUME_DIST`) and the offset functions `LAG`/`LEAD` ignore the frame entirely. Aggregates used as window functions, and `FIRST_VALUE`/`LAST_VALUE`/`NTH_VALUE`, are the ones whose answers move when you change the frame — those are the calls where the default matters.