MIN, MAX and Non-Numeric Aggregation
Aggregating dates and strings, and the meaning of MIN over text.
MIN, MAX and Non-Numeric Aggregation is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
MIN and MAX Beyond Numbers
Candidates assume MIN and MAX only work on numbers. Interviewers love to ask: "What does MAX(name) return? What about MIN(order_date)?"
The truth: MIN and MAX work on any orderable type — numbers, dates, and strings. Understanding the ordering rules for each type is the real skill being tested here.
The Numeric Case
Start with the familiar. On numeric columns, MIN returns the smallest value and MAX the largest. Simple.
Like the other aggregates, MIN and MAX ignore NULLs. A NULL is never the minimum or maximum; it is simply skipped. If every value is NULL, both return NULL.
SELECT MIN(salary) AS lowest, MAX(salary) AS highest
FROM employees;MIN and MAX on Dates
Dates and timestamps are fully orderable, so MIN(order_date) gives the earliest date and MAX(order_date) the latest.
This is how you answer "when was the first order placed?" (MIN) and "what is the most recent activity?" (MAX) without sorting and limiting. It is the idiomatic, set-based solution.
SELECT
MIN(order_date) AS first_order,
MAX(order_date) AS latest_order
FROM orders;Date Span in One Query
A common follow-up: "How long has this customer been active?" Combine MIN and MAX, then subtract.
This computes the span between first and last order. The exact subtraction syntax varies by dialect, but the MIN/MAX idea is universal and shows you can express ranges declaratively.
SELECT
customer_id,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order,
MAX(order_date) - MIN(order_date) AS active_days
FROM orders
GROUP BY customer_id;MIN and MAX on Strings
Here is the part that surprises people. On text columns, MIN and MAX use lexicographic (alphabetical) ordering based on the column's collation.
So MIN(name) returns the name that sorts first alphabetically, and MAX(name) the one that sorts last. MIN over {'Carol', 'Alice', 'Bob'} returns 'Alice'; MAX returns 'Carol'.
SELECT MIN(name) AS first_alpha, MAX(name) AS last_alpha
FROM employees;
-- with Alice, Bob, Carol: 'Alice' and 'Carol'Collation and Case Sensitivity
String ordering depends on collation, which controls case sensitivity and locale rules. Under many default collations, uppercase letters can sort before lowercase, so MAX of {'apple', 'Banana'} might surprise you.
In an interview, mention that string MIN/MAX results are collation-dependent. That nuance separates a memorized answer from genuine understanding.
What MIN(text) Does NOT Mean
A frequent misconception: that MIN(name) returns the shortest string or MAX(name) the longest. It does not. It is purely about sort order, not length.
If you actually need the longest string, you order by LENGTH(name) and pick the top row, or use MAX(LENGTH(name)) for just the length. Do not confuse value ordering with length.
SELECT MAX(LENGTH(name)) AS longest_name_length
FROM employees;MIN/MAX Do Not Combine Columns
Important gotcha: MIN and MAX operate on a single column independently. They do not return the whole row that contains the min or max.
If you write SELECT MIN(salary), name without grouping, you do not get the name of the lowest earner — you get an error or an arbitrary name depending on the dialect. To fetch the full row, use ORDER BY + LIMIT or a window function.
-- Wrong assumption: this does NOT give the lowest earner's name.
-- Correct approach to get the whole row:
SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 1;NULL Behavior Recap for MIN/MAX
Just like SUM and AVG, MIN and MAX skip NULLs. A NULL is neither the smallest nor the largest value.
- Mixed values + NULLs: NULLs ignored, real min/max returned.
- All NULLs or no rows: MIN and MAX return NULL.
This consistency across aggregates is a clean point to state: "All the standard aggregates ignore NULLs."
Putting It in a Group
MIN and MAX shine with GROUP BY for per-group extremes. "Highest salary per department" is a textbook example.
This returns one row per department with that department's top salary. Remember: it gives the value, not the employee. Pairing the name with it is a separate (and common) follow-up problem.
SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department;How to Answer Confidently
A strong interview answer: "MIN and MAX work on any orderable type. For numbers it's smallest/largest, for dates earliest/latest, for strings it's alphabetical by the column's collation. They ignore NULLs and return NULL only when no non-NULL value exists. They return a single value, not the full row, so to fetch the row I'd use ORDER BY with LIMIT or a window function."
Quick Check
Reason about string aggregation.
Recap
Everything about MIN and MAX:
- Work on any orderable type: numbers, dates, strings.
- Strings use alphabetical/collation order, not length.
- Dates: MIN = earliest, MAX = latest.
- They ignore NULLs; all-NULL or no rows returns NULL.
- They return a value, not the whole row — use ORDER BY + LIMIT for that.
Next: what happens when you use aggregates without a GROUP BY.
Frequently asked questions
Is the “MIN, MAX and Non-Numeric Aggregation” lesson free?
Yes — the full text of “MIN, MAX and Non-Numeric Aggregation” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “MIN, MAX and Non-Numeric Aggregation”?
Aggregating dates and strings, and the meaning of MIN over text. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “MIN, MAX and Non-Numeric Aggregation” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
- SUM and AVG with NULLs
- MIN, MAX and Non-Numeric Aggregation
- Aggregates Without GROUP BY