MAX / MIN

-- Returns the maximum value in a column.
SELECT MAX(column_name) FROM table_name;

-- Returns the minimum value in a column.
SELECT MIN(column_name) FROM table_name;

-- Returns the maximum and minimum values per group.
SELECT group_column, MAX(column_name), MIN(column_name) FROM table_name GROUP BY group_column;

-- Returns the highest and lowest salaries.
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;

-- Returns the highest and lowest salary per department.
SELECT department, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;

-- Returns the most recent hire date.
SELECT MAX(hire_date) AS latest_hire_date FROM employees;

-- Returns the price of the most and least expensive products.
SELECT MAX(price) AS highest_price, MIN(price) AS lowest_price FROM products;

-- Returns the alphabetically last name (string maximum).
SELECT MAX(name) AS max_name FROM employees;

-- Returns the name of the employee with the highest salary (using a subquery).
SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

-- Result of: SELECT department, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
-- FROM employees GROUP BY department;
-- +------------+----------------+---------------+
-- | department | highest_salary | lowest_salary |
-- +------------+----------------+---------------+
-- | Dev        |         380000 |        260000 |
-- | Sales      |         340000 |        250000 |
-- | Admin      |         320000 |        280000 |
-- +------------+----------------+---------------+

This syntax works the same across all major databases.

SELECT department, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;

How you retrieve other columns from the row that holds the maximum or minimum value differs by database. MySQL and SQLite require a subquery, while PostgreSQL, Oracle, and SQL Server support FETCH FIRST and window functions.

-- Works in all databases (subquery)
SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- PostgreSQL, Oracle (12c+), SQL Server (2012+)
-- Using a window function (also handles ties)
SELECT name, salary FROM (
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked WHERE rnk = 1;

MAX and MIN work on strings (lexicographic order) and dates (chronological order), not just numbers. For strings, the result is determined by character code order (which closely follows alphabetical order for UTF-8 encoded text).

MAX and MIN ignore NULL values. If all values in the column are NULL, the result is also NULL.

To retrieve other columns from the row that holds the maximum or minimum value, the common approach is to find the max or min with a subquery and then filter with WHERE. If multiple rows share the same value, all of them are returned. For totals and averages, see SUM / AVG.