MIN

MIN: The Smallest Value

MIN(column) returns the smallest non-NULL value in a column. It works on numbers, but also on dates, times, and strings — anywhere an ordering ("less than") is defined.

sql
SELECT MIN(amount) FROM orders;

With amounts 250.00, NULL, 400.00, 150.00, 300.00:

MIN(amount)
------------
     150.00

NULL is ignored, as always — MIN never returns NULL unless every value is NULL or the set is empty.

MIN on Dates

sql
SELECT MIN(order_date) FROM orders;
-- 2024-01-05   (the earliest order)

This is one of the most common real-world uses of MIN: "when was the first order placed?", "what's the earliest hire date?", "what's the oldest unresolved ticket?".

MIN on Strings

sql
SELECT MIN(customer_name) FROM orders;
-- 'Alice'   (alphabetically first among Alice, Bob, Alice, Charlie, Bob)

String comparison is lexicographic (dictionary-order), following the column's collation — by default this is typically case-sensitive or case-insensitive depending on the database's collation settings, which can make 'Apple' sort before or after 'apple' depending on configuration.

MIN with a Filter

sql
SELECT MIN(amount) FROM orders WHERE status = 'completed';
-- MIN(250.00, 400.00, 300.00) = 250.00

MIN vs a Sort-and-Limit Approach

MIN(amount) is equivalent in result to SELECT amount FROM orders ORDER BY amount ASC LIMIT 1, but conceptually different: MIN returns just the value, is a true aggregate (collapses to one row, works cleanly with GROUP BY per group in Chapter 15), and typically lets the engine use an index more efficiently for a single global minimum. The ORDER BY ... LIMIT 1 approach is more flexible when you also need other columns from that same row (e.g. "which customer placed the cheapest order?") — MIN alone can't tell you which row it came from.

Edge Cases

• MIN() over an empty result set (or an all-NULL column) returns NULL.
• MIN(DISTINCT column) is legal but pointless — the minimum of a set of distinct values is identical to the minimum of the same set with duplicates, so DISTINCT never changes a MIN (or MAX) result.
• Comparing MIN across mixed-case strings, or across different DATE/TIME types, depends entirely on the database's collation and type rules — always confirm behavior on your specific engine when precision matters.

Key Takeaways / Interview Q&A

Q: Does MIN() work on non-numeric columns? A: Yes — MIN works on any type with a defined ordering: numbers, dates, times, and strings (using lexicographic/collation-based ordering).

Q: What does MIN() return on an empty set? A: NULL.

Q: Does MIN(DISTINCT column) differ from MIN(column)? A: No — removing duplicates never changes which value is smallest, so DISTINCT has no effect on MIN (or MAX).

Q: If you need MIN(amount) AND the customer who placed that order, can MIN alone give you both? A: No — MIN only returns the value itself. To also see other columns from that row, use ORDER BY ... LIMIT 1, or join back to filter WHERE amount = (SELECT MIN(amount) ...).