Sorting a column of size labels held as codes can give three different orders — what decides which one you get?

## Three places an order can come from A plain text column has exactly one candidate order: the one the values themselves define. A **dictionary-coded column** — each distinct value held once, with a short whole number per row pointing at it — has three, because it now carries structure the plain column did not. | Source of the order | How the order is decided | small, medium, large comes back as | |---|---|---| | The values themselves | The values are compared as values | whatever their natural comparison gives — for text, alphabetical: large, medium, small | | An order declared on the value set | You state the order when the set is defined, and it overrides the values' own | small, medium, large | | The stored codes | Codes are compared as whole numbers, and are usually assigned as values are first encountered | whatever order the rows happened to arrive in | All three are defensible designs. None of them is *the* answer, which is why the question is worth asking: a candidate who says 'it sorts by the values' has only ever used one design and has not noticed. ## And a fourth behaviour: refusing Some designs treat a value set with no declared order as genuinely unordered, and refuse an ordering comparison such as *greater than medium* rather than answering it from the codes. This is the safest of the four behaviours and the one that surprises people most, because the same expression worked on the plain text column and stopped working the moment the column was coded. Refusal is not a bug to route around; it is the design telling you it has no order to give and wants you to state one. ## Why it bites in production The failure is quiet. An ordered report lists sizes in the wrong sequence, a minimum or a maximum returns the wrong member, or a condition such as *at least medium* selects a set nobody checked. Nothing raises, because every one of the three orders is a real order — just not the one the business meant. And the code-based order has a nastier property than the value-based one: it depends on the data, so the same program can produce two different orders on two different input files, and a change of upstream sort order silently reorders your output. ## How to tell which one you have 1. **Read the column's value set** and check whether it carries a declared order at all. If it does, that is almost certainly what the comparison is using. 2. **Ask the column for its minimum or maximum** and see whether it refuses. A refusal tells you the design will not order an unordered value set, which is useful information rather than an obstacle. 3. **Build the same values twice, in two different arrival orders, and sort both.** If the two results differ, the comparison is consulting the stored codes. If they agree but the order is alphabetical rather than the one you meant, it is consulting the values. That third test is the one worth remembering, because it distinguishes the two wrong answers from each other without reading any documentation. ## Making the order a property of the column The durable repair is to declare the order on the value set where the table is built, so it travels with the column instead of being re-established at each use site. Where a design does not offer a declared order, the alternatives are to carry a separate small column holding a rank per value and sort on that, or to decode and sort the values with an explicit comparison. Both work; both have to be repeated everywhere the column is used, and either one is forgotten eventually. A few related points worth having ready: - **Declaring the order is separate from declaring the set.** A design can let you state which values are permitted without letting you state how they compare, and then equality is safe while ordering is not. - **Adding a member later does not necessarily place it.** If a new size arrives, a declared order has to say where it goes; a code-based order will simply put it wherever it was first seen, which is usually last. - **Equality is unaffected by all of this.** Whichever source defines the order, testing a row against one particular value is the same question and gives the same answer. ## What the interviewer is listening for The sentence they want is *it depends where the order comes from*, followed by the three sources named. A candidate who then says which one they would rely on, and how they would check it on an unfamiliar column, has demonstrated the thing this question exists to find: that they know one column can sort three different ways, and that they have been caught by it.