Aggregate Functions - Learning Module

Loading content...

0/252

MIN and MAX: Finding Extreme Values

The Quest for Extremes

Data analysis often hinges on identifying boundaries. What's the highest salary? The earliest order date? The lowest-rated product? The most expensive transaction? These questions seek extreme values—the minimum and maximum points that define the range of your data.

MIN() and MAX() are SQL's tools for finding these extremes. While conceptually simple, they possess nuances that distinguish expert usage from naive queries—particularly regarding NULL handling, data type behavior, and performance optimization. Understanding these functions deeply enables efficient boundary detection, range analysis, and data validation.

What You Will Learn

By the end of this page, you will understand MIN and MAX semantics across different data types, their NULL handling behavior, performance characteristics with indexes, and practical patterns for range analysis, gap detection, and boundary validation.

MIN and MAX Fundamentals

MIN() returns the smallest value in a set, while MAX() returns the largest. Unlike COUNT, SUM, and AVG, these functions don't combine values mathematically—they select existing values based on ordering.

Basic syntax:

MIN(expression)
MAX(expression)

The expression can be a column, a computed expression, or any value that supports ordering comparison.

min_max_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Basic minimum and maximum
SELECT 
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;
 
-- Range calculation
SELECT 
    MIN(salary) AS min_sal,
    MAX(salary) AS max_sal,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees;
 
-- Multiple MIN/MAX in one query
SELECT 
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
WHERE department = 'Engineering';
 
-- MIN/MAX with expressions
SELECT 
    MIN(quantity * unit_price) AS smallest_order,
    MAX(quantity * unit_price) AS largest_order
FROM order_items;

Return Type Preservation

Unlike SUM and AVG, MIN and MAX return the same data type as the input. If you MIN a DATE column, you get a DATE. If you MAX an INTEGER, you get an INTEGER. They select actual values rather than computing new ones.

The ordering semantics:

MIN and MAX rely on the database's ordering rules for the data type:

Data Type	MIN Returns	MAX Returns
Numeric	Smallest number	Largest number
String	First in collation order	Last in collation order
Date/Time	Earliest date/time	Latest date/time
Boolean	FALSE (if exists)	TRUE (if exists)

For strings, "smallest" and "largest" depend on the collation (sorting rules). In case-sensitive collation, 'Z' < 'a' because uppercase letters sort before lowercase in ASCII/Unicode.

NULL Handling: Consistent Exclusion

Like all standard aggregate functions, MIN and MAX ignore NULL values. NULLs are excluded from consideration when determining extremes.

This behavior makes sense philosophically: NULL means "unknown," and we can't determine if an unknown value is greater or less than known values.

MIN/MAX NULL Behavior Examples
Data	MIN Result	MAX Result	Notes
100, 200, 300	100	300	Standard behavior
100, NULL, 300	100	300	NULL ignored
NULL, NULL, NULL	NULL	NULL	All NULL returns NULL
(empty set)	NULL	NULL	No rows returns NULL

min_max_nulls.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Sample data with NULLs
-- salaries: 100, NULL, 300, 200, NULL
 
SELECT 
    MIN(salary) AS min_sal,    -- 100 (NULLs ignored)
    MAX(salary) AS max_sal     -- 300 (NULLs ignored)
FROM employees;
 
-- Empty set behavior
SELECT 
    MIN(salary) AS min_sal,    -- NULL
    MAX(salary) AS max_sal     -- NULL
FROM employees
WHERE 1 = 0;  -- No matching rows
 
-- Safe handling with COALESCE
SELECT 
    COALESCE(MIN(salary), 0) AS safe_min,
    COALESCE(MAX(salary), 0) AS safe_max
FROM employees
WHERE department = 'NonExistent';
 
-- Checking for complete NULL data
SELECT 
    CASE 
        WHEN MIN(salary) IS NULL THEN 'No salary data'
        ELSE 'Salary data exists'
    END AS data_status
FROM employees;

NULL as Extreme Value?

If you want to treat NULL as a specific value (like 0 for MIN or infinity for MAX), wrap the column in COALESCE: MIN(COALESCE(column, 0)) treats NULLs as 0s. However, this changes the semantics significantly—use with caution.

Data Type Considerations

MIN and MAX work across all comparable data types, but their behavior varies based on the type's ordering rules. Understanding these variations is essential for correct usage.

Numeric types:

For numbers, MIN and MAX follow natural mathematical ordering. Negative numbers are less than positive, and decimal precision is preserved.

min_max_numeric.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Numeric MIN/MAX
SELECT 
    MIN(temperature) AS coldest,   -- Handles negatives correctly
    MAX(temperature) AS hottest
FROM weather_data;
-- If data is -10, 5, 15: MIN = -10, MAX = 15
 
-- Decimal precision
SELECT 
    MIN(price) AS lowest_price,    -- Preserves decimal precision
    MAX(price) AS highest_price
FROM products;
-- If data is 9.99, 19.95, 29.99: MIN = 9.99, MAX = 29.99

String types:

String ordering depends on the collation (locale-specific sorting rules). This can lead to surprising results if you're not careful.

min_max_strings.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- String MIN/MAX follows collation order
SELECT 
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically
FROM employees;
-- Depends on collation: case-sensitive vs case-insensitive
 
-- Case sensitivity example (ASCII/binary collation)
-- Values: 'Apple', 'banana', 'CHERRY'
-- MIN = 'Apple' (A=65 < C=67 < b=98 in ASCII)
-- MAX = 'banana' (b=98 > all uppercase letters in ASCII)
-- This may surprise you!
 
-- Case-insensitive collation gives expected alphabetical order
SELECT 
    MIN(name COLLATE SQL_Latin1_General_CP1_CI_AS) AS first_name,
    MAX(name COLLATE SQL_Latin1_General_CP1_CI_AS) AS last_name
FROM products;  -- SQL Server syntax
 
-- PostgreSQL: specify collation
SELECT 
    MIN(name COLLATE "en_US.utf8") AS first_name
FROM products;

String Numeric Comparison Trap

If numeric values are stored as strings, MIN and MAX compare them lexicographically: '9' > '10' because '9' > '1'. Similarly, '2' > '100'. Always use proper numeric types for numbers, or CAST before comparing.

min_max_dates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Date/Time types (chronological ordering)
SELECT 
    MIN(order_date) AS first_order,
    MAX(order_date) AS most_recent_order
FROM orders;
 
-- Timestamp precision
SELECT 
    MIN(created_at) AS earliest_timestamp,
    MAX(updated_at) AS latest_activity
FROM audit_log;
 
-- Date range analysis
SELECT 
    MIN(birth_date) AS oldest_employee,
    MAX(birth_date) AS youngest_employee,
    AGE(MIN(birth_date)) AS oldest_age,
    AGE(MAX(birth_date)) AS youngest_age  -- PostgreSQL AGE function
FROM employees;

Boolean types:

Boolean MIN/MAX treats FALSE < TRUE (0 < 1). This is occasionally useful for checking if ANY or ALL values are true:

min_max_boolean.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Boolean MIN/MAX (in databases that support it)
SELECT 
    MIN(is_active) AS any_inactive,  -- FALSE if any row is FALSE
    MAX(is_active) AS any_active     -- TRUE if any row is TRUE
FROM accounts;
 
-- More common: use BOOL_AND / BOOL_OR (PostgreSQL)
SELECT 
    BOOL_AND(is_verified) AS all_verified,  -- TRUE only if ALL are TRUE
    BOOL_OR(is_suspended) AS any_suspended  -- TRUE if ANY is TRUE
FROM users;
 
-- SQL Server equivalent using MIN/MAX
SELECT 
    CASE MIN(CAST(is_active AS INT)) WHEN 0 THEN 'Some Inactive' ELSE 'All Active' END,
    CASE MAX(CAST(is_premium AS INT)) WHEN 1 THEN 'Has Premium' ELSE 'No Premium' END
FROM customers;

Performance and Index Utilization

MIN and MAX have unique performance characteristics among aggregate functions. Because they seek a single extreme value rather than processing all values, they can often be optimized using indexes.

Index optimization for MIN/MAX:

When an index exists on the column being aggregated, the database can often find MIN or MAX by simply reading the first or last entry in the index—an O(1) operation instead of O(n) full table scan.

This optimization applies when:

The query is SELECT MIN(indexed_col) FROM table or SELECT MAX(indexed_col) FROM table
There's no WHERE clause, or the WHERE clause is efficiently filterable
No GROUP BY is present (per-group extremes require more work)

min_max_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Fast: Uses index efficiently
-- Assuming index on 'created_at'
SELECT MAX(created_at) FROM orders;
-- Execution: Index scan to last entry, O(1) or O(log n)
 
-- Also fast: Simple WHERE with indexed column
SELECT MIN(order_date) FROM orders WHERE customer_id = 12345;
-- If (customer_id, order_date) index exists, very efficient
 
-- Slower: Complex WHERE may prevent optimization
SELECT MIN(price) FROM products WHERE LOWER(name) LIKE '%widget%';
-- LIKE with leading wildcard prevents index use on 'name'
-- Function on column (LOWER) may prevent index use
 
-- Slower: GROUP BY requires finding MIN/MAX per group
SELECT customer_id, MIN(order_date), MAX(order_date)
FROM orders
GROUP BY customer_id;
-- Must process each group, though indexes still help
 
-- Check execution plan to verify optimization
EXPLAIN ANALYZE SELECT MAX(created_at) FROM orders;

EXPLAIN Your Queries

Use EXPLAIN or EXPLAIN ANALYZE to verify MIN/MAX optimization. Look for 'Index Only Scan' or 'Index Scan with LIMIT' patterns indicating the query isn't scanning all rows.

MIN/MAX Performance Comparison
Scenario	Without Index	With Index	Notes
Simple MIN/MAX	O(n) - full scan	O(1) or O(log n)	Index boundary access
With WHERE (indexed)	O(n) - full scan	O(m log n) where m matches	Index seek + boundary
With GROUP BY	O(n log g)	O(n) or better	g = number of groups
Expression MIN/MAX	O(n) - always	O(n) - can't use index	Functions prevent optimization

min_max_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Optimization: Combined MIN/MAX in single scan
SELECT MIN(price), MAX(price) FROM products;
-- Database processes table once, tracking both
 
-- Anti-pattern: Separate queries
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
-- Two separate scans (unless cached)
 
-- Optimization: Correlated scalar subquery for "row with MAX"
-- Find the employee with highest salary
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
 
-- Or use window function (often more efficient)
SELECT *
FROM (
    SELECT *, MAX(salary) OVER () AS max_sal
    FROM employees
) sub
WHERE salary = max_sal;
 
-- Or ORDER BY with LIMIT (very efficient with index)
SELECT * FROM employees ORDER BY salary DESC LIMIT 1;

MIN and MAX with GROUP BY

Combining MIN/MAX with GROUP BY enables segmented extreme analysis—finding minimums and maximums within each category, time period, or other dimensional slice.

min_max_group_by.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Salary range by department
SELECT 
    department_id,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_spread
FROM employees
GROUP BY department_id
ORDER BY salary_spread DESC;
 
-- Date range by customer (account lifespan)
SELECT 
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    MAX(order_date) - MIN(order_date) AS days_between_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;  -- Only customers with multiple orders
 
-- Price extremes by category and year
SELECT 
    category,
    EXTRACT(YEAR FROM sale_date) AS year,
    MIN(unit_price) AS lowest_price,
    MAX(unit_price) AS highest_price,
    AVG(unit_price) AS avg_price
FROM sales
GROUP BY category, EXTRACT(YEAR FROM sale_date)
ORDER BY category, year;

Finding the row containing MIN/MAX values:

A common challenge is retrieving the entire row that contains the minimum or maximum value, not just the extreme value itself.

min_max_row_retrieval.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Goal: Find the full record of highest-paid employee per department
 
-- Approach 1: Correlated subquery (portable but can be slow)
SELECT *
FROM employees e1
WHERE salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);
 
-- Approach 2: Join with aggregated subquery
SELECT e.*
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) m ON e.department_id = m.department_id AND e.salary = m.max_salary;
 
-- Approach 3: Window function (usually most efficient)
SELECT *
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;
 
-- Approach 4: DISTINCT ON (PostgreSQL specific)
SELECT DISTINCT ON (department_id) *
FROM employees
ORDER BY department_id, salary DESC;

Ties Handling

If multiple rows share the MIN or MAX value, approaches 1 and 2 return all ties. Approach 3 with RANK also returns ties. To get exactly one row per group, use ROW_NUMBER() instead of RANK(), or DISTINCT ON in PostgreSQL.

Practical Patterns and Use Cases

MIN and MAX appear in numerous practical patterns beyond simple extreme finding. Let's explore common use cases.

min_max_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Pattern: Range validation
-- Check if values fall within expected bounds
SELECT 
    CASE 
        WHEN MIN(quantity) < 0 THEN 'Invalid: Negative quantities found'
        WHEN MAX(quantity) > 10000 THEN 'Warning: Unusually large quantities'
        ELSE 'All quantities within expected range'
    END AS validation_result
FROM order_items;
 
-- Pattern: Gap detection
-- Find missing numbers in a sequence
SELECT 
    MIN(id) AS first_id,
    MAX(id) AS last_id,
    COUNT(*) AS actual_count,
    MAX(id) - MIN(id) + 1 AS expected_count,
    (MAX(id) - MIN(id) + 1) - COUNT(*) AS gap_count
FROM invoice_ids;
 
-- Pattern: Freshness check
-- How recent is our data?
SELECT 
    MAX(updated_at) AS last_update,
    CURRENT_TIMESTAMP - MAX(updated_at) AS time_since_update,
    CASE 
        WHEN MAX(updated_at) < CURRENT_TIMESTAMP - INTERVAL '1 hour' THEN 'STALE'
        ELSE 'FRESH'
    END AS data_status
FROM sensor_readings;

min_max_patterns_2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Pattern: Generating sequences with bounds
-- Create a date series covering all order dates
SELECT 
    generate_series(
        (SELECT MIN(order_date) FROM orders)::date,
        (SELECT MAX(order_date) FROM orders)::date,
        '1 day'::interval
    ) AS date_in_range;
 
-- Pattern: Conditional MIN/MAX
SELECT 
    MIN(CASE WHEN status = 'completed' THEN order_date END) AS first_completed,
    MAX(CASE WHEN status = 'completed' THEN order_date END) AS last_completed,
    MIN(CASE WHEN status = 'pending' THEN order_date END) AS oldest_pending
FROM orders;
 
-- Pattern: Running MIN/MAX (window functions)
SELECT 
    date,
    price,
    MIN(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_min,
    MAX(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max
FROM stock_prices;
 
-- Pattern: High-water mark (rolling maximum)
SELECT 
    date,
    revenue,
    MAX(revenue) OVER (ORDER BY date) AS revenue_high_water_mark,
    revenue - MAX(revenue) OVER (ORDER BY date) AS drawdown_from_peak
FROM daily_revenue;

Aggregate FIRST/LAST pattern:

While SQL doesn't have built-in FIRST/LAST aggregates in most databases, you can simulate them:

first_last_pattern.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Get the first and last value when ordered by another column
-- Goal: For each customer, get first purchase amount and last purchase amount
 
-- Using window functions
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS first_purchase,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_purchase
FROM orders;
 
-- Alternative: Subqueries with MIN/MAX date
SELECT 
    customer_id,
    (SELECT amount FROM orders o2 
     WHERE o2.customer_id = o.customer_id 
     ORDER BY order_date LIMIT 1) AS first_purchase,
    (SELECT amount FROM orders o2 
     WHERE o2.customer_id = o.customer_id 
     ORDER BY order_date DESC LIMIT 1) AS last_purchase
FROM (SELECT DISTINCT customer_id FROM orders) o;
 
-- PostgreSQL: ARRAY_AGG with subscript
SELECT 
    customer_id,
    (ARRAY_AGG(amount ORDER BY order_date))[1] AS first_purchase,
    (ARRAY_AGG(amount ORDER BY order_date DESC))[1] AS last_purchase
FROM orders
GROUP BY customer_id;

Best Practices and Common Pitfalls

MIN/MAX Best Practices

•Index columns used in MIN/MAX: Especially for standalone queries without GROUP BY, indexes enable O(1) lookups.
•Combine MIN and MAX in single queries: One scan retrieves both extremes, more efficient than separate queries.
•Use window functions for per-row extremes: When you need extremes alongside detail data, window functions avoid self-joins.
•Consider DISTINCT for finding row with extreme: Use ROW_NUMBER() or DISTINCT ON to get exactly one row per group.
•Validate data ranges: MIN/MAX are excellent for data quality checks—verify values fall within expected bounds.

Common Pitfalls to Avoid

•String numeric confusion: MIN('9', '10') = '10' because '1' < '9' lexicographically. Cast to numeric first!
•Collation surprises: MIN/MAX on strings depends on collation; 'Z' < 'a' in binary collation.
•Expecting zero from empty sets: MIN/MAX return NULL, not 0, for empty results—use COALESCE if needed.
•Ignoring ties: When retrieving rows with MAX value, remember multiple rows may share that value.
•Forgetting expression recomputation: MIN(expression) evaluates the expression for each row; ensure it's not expensive.

The String-Number Trap

If you store order numbers as VARCHAR and query MIN(order_number), you'll get '1001' when '999' is actually the largest numeric value. Always use appropriate data types: integers for numbers, dates for dates, etc.

Summary: Mastering MIN and MAX

MIN and MAX are fundamental tools for boundary detection and range analysis. Let's consolidate the key concepts:

Key Takeaways

•MIN/MAX ignore NULLs — Consistent with other aggregates. Empty sets return NULL.
•Type preservation — Unlike SUM/AVG, MIN/MAX return the same type as the input column.
•Ordering is type-dependent — Strings use collation order, which may differ from intuitive alphabetical order.
•Index optimization — Simple MIN/MAX queries can be O(1) with proper indexes; verify with EXPLAIN.
•Retrieving extreme rows — Use window functions (RANK, ROW_NUMBER) or DISTINCT ON to get full rows with extreme values.
•Practical patterns — Range validation, gap detection, freshness checks, and running extremes are common applications.

What's next:

We've now covered all five core aggregate functions: COUNT, SUM, AVG, MIN, and MAX. The final topic in this module addresses a critical cross-cutting concern: how all aggregates handle NULL values. We'll consolidate NULL handling rules and explore advanced patterns for dealing with missing data in aggregation.

Page Complete

You now understand MIN and MAX comprehensively—their NULL handling, data type behaviors, performance characteristics, and practical patterns for range analysis. These tools are essential for data validation, boundary detection, and trend analysis.