What do MIN and MAX return for VARCHAR and DATE columns, and what decides the order?

## The signature `MIN` and `MAX` are the two core aggregates that are not arithmetic. They require only that the argument's type has a defined ordering, which in practice covers numeric types, character strings, `DATE`, `TIME` and `TIMESTAMP`. They return the extreme value **in the argument's own declared type** — no promotion, no conversion: ```sql SELECT MIN(hire_date) AS first_hire, -- a DATE MAX(hire_date) AS latest_hire, -- a DATE MIN(city) AS first_city -- a character string FROM employees; ``` That is why they are the workhorses of "earliest", "latest", "alphabetically first" questions, and why they show up in far more real queries than `SUM` does. ## Dates and timestamps: chronological, and unambiguous Temporal types order chronologically, so `MAX(order_date)` is the most recent order date and `MIN(order_date)` is the oldest. There is no ambiguity in the ordering itself. Two details matter in practice: - **A `DATE` is not a `TIMESTAMP`.** `MAX(order_date)` over a `DATE` column gives a day, not the last moment of that day. Comparing it against a timestamp requires an explicit conversion, and mixing them in a predicate is where off-by-one-day bugs live. - **Time zones follow the type.** For `TIMESTAMP WITH TIME ZONE`, the comparison is on the underlying instant; for `TIMESTAMP` without a zone, it is on the wall-clock value as stored. `MAX` does not reconcile zones for you. ## Character data: the collation decides everything For character types, the ordering is not a property of SQL but of the **collation** attached to the column, expression or database. A collation defines the comparison rules: whether `'apple'` sorts before or after `'Banana'`, how accented characters compare with unaccented ones, and how digits and punctuation rank against letters. The consequence is uncomfortable but important: `MAX(city)` over identical data can legitimately return different answers on two databases with different collations. A binary collation compares raw code points, so all uppercase letters precede all lowercase ones and `'Zebra' < 'apple'`. A linguistic collation typically compares case-insensitively at the primary level, so `'apple' < 'Zebra'`. If a query's result must be stable across environments, the collation is part of that contract, not an implementation detail. ## The numbers-as-text trap This is the version of the question interviewers actually ask: ```sql -- releases.version_label is VARCHAR and holds '9', '10', '11' SELECT MAX(version_label) FROM releases; -- '9' ``` String comparison is positional: it compares the first characters, and `'9'` is greater than `'1'`, so `'9'` wins outright and the remaining characters are never examined. The same effect makes `'100' < '99'` and makes `MAX(invoice_no)` wrong whenever invoice numbers are stored as text without zero padding. There are two honest fixes. Cast the argument, accepting that a non-numeric value anywhere in the column will raise an error: ```sql SELECT MAX(CAST(version_label AS INTEGER)) FROM releases; -- 11 ``` Or fix the storage: store numbers in a numeric column, or, if the identifier must be text, zero-pad it to a fixed width so that lexicographic order and numeric order coincide. Fixed-width zero padding is the reason well-designed text keys look like `'0000000011'`. ## Two more edges worth knowing **MIN and MAX in one row do not come from one row.** `SELECT MIN(price), MAX(created_at) FROM products` computes each extreme independently over the group. The cheapest product is very likely not the most recently created one, and the result row is not a product. Candidates who assume the pair describes a single row produce confidently wrong reports. **Fixed-length CHAR comparison may ignore trailing spaces.** Under a PAD SPACE collation, `'ab'` and `'ab '` compare equal, so which of them `MAX` reports for a `CHAR` column can be arbitrary. `VARCHAR` under a NO PAD collation treats the trailing spaces as significant. If trailing whitespace can occur in your data, trim at write time rather than reasoning about it at read time. As with the other aggregates, NULL inputs are ignored, so the extreme is taken over the values that are present. ## Answering it well Lead with the type rule — same type in, same type out — then say the ordering comes from the type: chronological for temporal, collation-defined for text. Offer the `'9'` versus `'11'` example unprompted; it proves you have met the bug rather than memorised the definition. Finish with the fix that matches the situation: cast for a one-off query, fix the column type or pad the key for a system you own.