SQL MIN() and MAX()

The MIN() and MAX() functions are SQL’s aggregate tools for finding the smallest and largest value in a column. Instead of manually scanning through rows to spot the lowest price or the most recent hire date, you let the database engine do it and return a single answer. They work with numbers, text, and dates alike, and become even more powerful when combined with GROUP BY to find extremes within categories rather than across an entire table.

Overview: How MIN() and MAX() Work

MIN() and MAX() are aggregate functions: they collapse many rows down into a single value. Given a column, MIN() returns the smallest value found and MAX() returns the largest. Unlike scalar functions such as UPPER() or ROUND() that transform each row individually, aggregate functions look at a whole set of rows – or a group of rows, when used with GROUP BY – and produce one summarizing result per group.

Internally, the engine scans every row that survives the WHERE clause and keeps a running “smallest seen so far” or “largest seen so far” as it goes, updating it whenever a new row beats the current record. If the column has an index, many engines (including SQLite, MySQL, and PostgreSQL) can skip the full scan entirely: for a plain MIN(column) or MAX(column) with no WHERE clause, the optimizer can simply read the first or last entry of the index’s B-tree and return it immediately, without touching every row. This is why MIN()/MAX() on an indexed column is typically instant even on huge tables, while the same query on an unindexed column requires a full table scan.

MIN()/MAX() are not limited to numbers. Text columns are compared using the collation’s sort order (so MIN(name) returns whichever value sorts first, generally alphabetically), and ISO-formatted date strings ('YYYY-MM-DD') compare correctly as text, so MIN(hire_date) correctly returns the earliest date. Both functions ignore NULL values entirely – a NULL is “unknown,” not infinitely large or small, so it never wins or loses a comparison. If every value in the column is NULL, or the table/group has zero rows, MIN()/MAX() return NULL rather than raising an error.

Syntax

The general form pairs MIN() or MAX() with a column, optionally scoped by WHERE, GROUP BY, and HAVING:

SELECT MIN(price) AS min_price, MAX(price) AS max_price
FROM products
WHERE category = 'Furniture';

Part	Meaning
MIN(column)	Returns the smallest non-NULL value of column across the matched rows.
MAX(column)	Returns the largest non-NULL value of column across the matched rows.
AS alias	Optional; names the result column so it’s easier to reference from application code.
WHERE	Filters individual rows before the aggregate is computed – narrows which rows count.
GROUP BY	Computes a separate MIN/MAX for each group instead of one value for the whole table.
HAVING	Filters entire groups after aggregation, based on the aggregated value itself.

This example returns only Furniture’s cheapest and priciest item, because the WHERE clause removes every other category’s rows before MIN()/MAX() ever see them.

Examples

Example 1: Overall Minimum and Maximum

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price REAL,
  category TEXT
);

INSERT INTO products VALUES
  (1, 'Laptop', 999.99, 'Electronics'),
  (2, 'Mouse', 19.99, 'Electronics'),
  (3, 'Desk', 249.50, 'Furniture'),
  (4, 'Chair', 89.00, 'Furniture'),
  (5, 'Monitor', 199.99, 'Electronics');

SELECT MIN(price) AS cheapest_price, MAX(price) AS priciest_price
FROM products;

Result:

cheapest_price	priciest_price
19.99	999.99

With no WHERE or GROUP BY, the whole table is treated as a single group. The engine compares all five prices and reports the Mouse’s 19.99 as the minimum and the Laptop’s 999.99 as the maximum – just two numbers back from five rows of input.

Example 2: MIN/MAX Per Group with GROUP BY

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price REAL,
  category TEXT
);

INSERT INTO products VALUES
  (1, 'Laptop', 999.99, 'Electronics'),
  (2, 'Mouse', 19.99, 'Electronics'),
  (3, 'Desk', 249.50, 'Furniture'),
  (4, 'Chair', 89.00, 'Furniture'),
  (5, 'Monitor', 199.99, 'Electronics');

SELECT category, MIN(price) AS min_price, MAX(price) AS max_price
FROM products
GROUP BY category
ORDER BY category;

Result:

category	min_price	max_price
Electronics	19.99	999.99
Furniture	89.00	249.50

GROUP BY category splits the five rows into two buckets – Electronics (Laptop, Mouse, Monitor) and Furniture (Desk, Chair) – and computes MIN()/MAX() separately within each bucket, producing one output row per group instead of one row overall.

Example 3: Combining GROUP BY and HAVING

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary REAL,
  hire_date TEXT
);

INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 95000, '2019-03-15'),
  (2, 'Bob', 'Engineering', 88000, '2021-07-01'),
  (3, 'Carol', 'Sales', 72000, '2018-11-20'),
  (4, 'Dave', 'Marketing', 65000, '2022-01-10');

SELECT department,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary,
       MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000
ORDER BY department;

Result:

department	min_salary	max_salary	salary_range
Engineering	88000	95000	7000
Sales	72000	72000	0

Marketing is missing from the result even though it’s a real department in the table. Its only employee, Dave, earns 65000, so MAX(salary) for Marketing is 65000 – which fails the HAVING MAX(salary) > 70000 test and gets dropped after the grouping is computed. This is the key difference between WHERE (filters raw rows before grouping) and HAVING (filters groups after aggregation).

How It Works Step by Step

For the Example 3 query, the engine conceptually processes clauses in this logical order, regardless of the order you typed them:

• FROM – start with every row in employees (4 rows).
• WHERE – there is none here, so all 4 rows continue.
• GROUP BY department – split the rows into three groups: Engineering (Alice, Bob), Sales (Carol), Marketing (Dave).
• Aggregate computation – compute MIN(salary), MAX(salary), and the derived salary_range for each group.
• HAVING MAX(salary) > 70000 – discard groups whose aggregated max fails the test; Marketing (65000) is removed, Engineering and Sales survive.
• SELECT – project the requested columns for each surviving group.
• ORDER BY department – sort the two remaining groups alphabetically: Engineering, then Sales.

Notice that HAVING can only test values that are already aggregated (or grouped columns) – it runs after grouping, which is exactly why it can reference MAX(salary) while WHERE cannot.

Common Mistakes

Mistake 1: Using an aggregate function inside WHERE

It’s tempting to filter on MAX(salary) directly in WHERE, but WHERE runs before grouping and aggregation even happen, so the aggregate doesn’t exist yet at that stage:

SELECT department, MAX(salary) AS max_salary
FROM employees
WHERE MAX(salary) > 90000
GROUP BY department;

This raises an error such as “misuse of aggregate function MAX()” because WHERE filters individual rows, and a single row has no notion of a group’s maximum. The fix is to move the condition to HAVING, which runs after the aggregation:

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 90000;

This correctly returns only Engineering (95000), since Sales (72000) and Marketing (65000) don’t clear the 90000 bar.

Mistake 2: Selecting extra columns alongside MIN()/MAX() and expecting matching row detail

People often write a query like this hoping to also see whose salary is the minimum:

SELECT name, MIN(salary) FROM employees;

Some databases tolerate this and quietly return the row that happens to match the minimum, but it is not standard SQL: strict engines (PostgreSQL, SQL Server, and MySQL in strict mode) reject it outright, because name is neither aggregated nor listed in a GROUP BY, so the engine can’t guarantee which employee’s name to pair with the single aggregated salary. Relying on it makes your query non-portable and fragile. The reliable, portable fix is to sort and take the top row explicitly:

SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 1;

This returns Dave with a salary of 65000 – and by flipping to ORDER BY salary DESC you’d get Alice at 95000 instead. This pattern works identically across every major SQL engine.

Best Practices

• Add an index on any column you frequently run MIN()/MAX() against without a WHERE clause – the engine can often answer directly from the index without scanning the table.
• Use WHERE to filter rows before aggregation, and reserve HAVING only for conditions on the aggregated value itself.
• Never put an aggregate function directly inside WHERE – it belongs in HAVING or a subquery.
• Don’t mix non-aggregated, non-grouped columns with MIN()/MAX() in the same SELECT list – use ORDER BY ... LIMIT 1 or a subquery/window function instead when you need the whole row.
• Remember MIN()/MAX() ignore NULLs; if a column can be entirely NULL for a group, expect NULL back, not an error or zero.
• Alias your aggregate columns with AS so results are easy to read and reference in application code.

Practice Exercises

Exercise 1: Using the products table from Example 1, write a query that returns the cheapest and most expensive product price within the Electronics category only. (Hint: add a WHERE clause before aggregating.)

Exercise 2: Using the employees table from Example 3, write a query that returns the single department with the highest maximum salary, using GROUP BY together with ORDER BY and LIMIT rather than HAVING. (Expected answer: Engineering, 95000.)

Exercise 3: Using the same employees table, write a query with GROUP BY and HAVING that returns only departments whose salary range (MAX(salary) - MIN(salary)) is greater than 5000. (Expected answer: only Engineering, with a range of 7000.)

Summary

• MIN() and MAX() are aggregate functions that return the smallest and largest non-NULL value in a column.
• They work on numbers, text (sorted alphabetically), and date strings, and always ignore NULL values.
• An index on the target column lets the engine answer plain MIN()/MAX() queries without scanning the whole table.
• GROUP BY computes a separate MIN/MAX per group instead of one value for the entire table.
• WHERE filters rows before aggregation; HAVING filters groups after aggregation – aggregate functions cannot be used inside WHERE.
• To pair MIN/MAX with other row details portably, use ORDER BY ... LIMIT 1 or a subquery, not a bare mixed SELECT list.