MIN and MAX

Learning Focus

Use this lesson to find extreme values using MIN, MAX, and DuckDB's unique ARGMIN/ARGMAX functions.

Basic Usage

-- Salary extremes
SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;

-- Earliest and latest hire dates
SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire
FROM employees;

-- Per-department extremes
SELECT
    department,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department;

ARGMIN and ARGMAX (DuckDB-specific)

These return the value of another column at the row where min/max occurs:

-- Which employee has the lowest salary?
SELECT argmin(name, salary) AS lowest_paid FROM employees;

-- Which employee has the highest salary per department?
SELECT department, argmax(name, salary) AS top_earner
FROM employees
GROUP BY department;

MIN/MAX on Strings

-- Alphabetically first and last names
SELECT MIN(name) AS first_alpha, MAX(name) AS last_alpha FROM employees;

MIN/MAX on Dates

SELECT
    MIN(hire_date) AS first_hire,
    MAX(hire_date) AS most_recent_hire,
    MAX(hire_date) - MIN(hire_date) AS tenure_range
FROM employees;

Common Pitfalls

Pitfall	Consequence	Prevention
MIN/MAX ignores NULLs	May return surprising results	Use COUNT(col) to check for NULLs first
Using MIN without GROUP BY	Returns a single row	Combine with GROUP BY for per-group results
ARGMIN not in other databases	Portability issue	Document as DuckDB-specific

Quick Reference

SELECT MIN(col), MAX(col) FROM t;
SELECT argmin(name, col) FROM t;
SELECT argmax(name, col) FROM t;
SELECT dept, MAX(col) FROM t GROUP BY dept;

What's Next

• Next: STDDEV and Variance - Measure data spread and variability.
• Module Overview - Return to this module index.