A grouped total returns its keys in no obvious order — what does that say about the split?

## The question behind the question An interviewer asking this is checking one thing: do you know that a grouped result's order is produced by *how the groups were formed*, rather than being part of what a grouped operation promises? A candidate who answers "grouped results come back sorted" and a candidate who answers "grouped results come back in arbitrary order" are making the same mistake in opposite directions. ## Two ways to form groups | split mechanism | how groups are found | ordering left behind | typical cost shape | | --- | --- | --- | --- | | sorted split | the key column is ordered so equal keys sit in one contiguous run | an ordering by key falls out for free | pays an ordering cost up front, then sequential reads | | hashed split | each key value is dropped into a bucket by its hash | none at all | no ordering cost, scattered reads | Both produce exactly the same grouping — the same rows in the same groups — and differ in cost and in by-products. A third case sits on top of both: a tool may sort the keys *after* forming the groups, as a default or on request, in which case the result is ordered whichever mechanism ran underneath. ## First-appearance order is not the same as no order When a hashed split returns keys "unordered", what usually comes back is **first-appearance order**: the order in which each distinct key value was first encountered while sweeping the rows. That is not random, and that is exactly the trap. - It looks stable, because rerunning the same job over the same file reproduces it. - It has a plausible story behind it, so people rationalise it as intentional. - It is nevertheless an artefact of the bookkeeping, and nothing documents it as a contract. ## Why a by-product is dangerous in a way that a guarantee is not The failure mode is silent. Nothing raises, nothing warns, and the change is not in your code: 1. Something upstream re-orders the input, so first-appearance order changes and a report's rows move. 2. The key column's type changes, and the tool switches from a hashed to a sorted strategy or the reverse. 3. A tool version changes the default, or the planner picks a different strategy for the same statement at a different data size. 4. A downstream consumer — a chart, a file diff, a fixed test — was quietly built on the old order, and now disagrees with no error anywhere. The cost is rarely a wrong number. It is a diff that will not settle, a chart whose categories reshuffle between runs, or a test that fails on a machine where the data arrived differently. ## The rule State it as two clauses: - **Ordering in a grouped result is a by-product of the split mechanism.** Say which mechanism would produce which by-product if asked; do not state a flat outcome as though every tool behaved the same way. - **If order matters, make it explicit.** Order the result as a step of its own after the grouping, so the requirement lives in the code rather than in an implementation detail you did not choose. ## Two things this is not - It is **not** about whether equal keys kept their original relative order through the split. That is a property of the sort underneath, and a different subject. - It is **not** an argument for sorting the input before grouping. A sorted input does not oblige the split to preserve that order into the result, and the ordering cost is paid twice if the split orders the keys anyway. ## What good sounds like in the room "Whether the keys come back in order depends on how the split formed them. A sorted split leaves an ordering behind; a hashed split does not and will usually give you first-appearance order. Either way it is a by-product, so I would not build a consumer on it — if the report needs key order, I would order the result explicitly and let the reader see that requirement."