SQL MIN AND MAX

What are SQL MIN and MAX functions?

MIN and MAX are aggregate functions that return the smallest and largest non-NULL values from a set, respectively.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MIN AND MAX: Supported by PostgreSQL, MySQL, SQL Server, Oracle, SQLite, MariaDB, Snowflake, BigQuery, Redshift, IBM Db2, and all engines that comply with the SQL standard.

SQL MIN AND MAX Full Explanation

MIN and MAX are standard SQL aggregate functions used to identify the minimum and maximum values within a result set or each GROUP BY partition. They accept any comparable data type (numeric, date, text, etc.) and ignore NULL values by default. When used without GROUP BY, they return a single row summarizing the entire dataset; with GROUP BY, they compute per-group extremes. Some dialects allow the DISTINCT keyword inside the function to consider only unique values, though this rarely changes the result unless duplicates are present. MIN and MAX can also operate on expressions, including CASE statements, arithmetic calculations, or concatenations, as long as the resulting values are comparable. Because they are deterministic, MIN and MAX always produce the same output for identical input and have no side effects. They are frequently paired with ORDER BY or subqueries to retrieve full rows containing the extreme values. Performance is generally efficient if the target column is indexed, but large GROUP BY operations can still be costly. Be mindful that MIN and MAX ignore NULLs; if you need to treat NULL as the lowest or highest value, use COALESCE or ORDER BY with NULLS FIRST/LAST.

SQL MIN AND MAX Syntax

-- Overall minimum
SELECT MIN(column_name) AS min_value
FROM table_name;

-- Overall maximum
SELECT MAX(column_name) AS max_value
FROM table_name;

-- Per-group extremes
SELECT group_col,
       MIN(measure_col) AS min_measure,
       MAX(measure_col) AS max_measure
FROM   table_name
GROUP  BY group_col;

SQL MIN AND MAX Parameters

expression (Any comparable data type) - The value or expression to evaluate.
DISTINCT (keyword, optional) - Removes duplicate values before calculating the result (supported in most major databases).

Example Queries Using SQL MIN AND MAX

-- 1. Find the earliest order date
SELECT MIN(order_date) AS first_order
FROM   orders;

-- 2. Highest salary per department
SELECT department_id,
       MAX(salary) AS top_salary
FROM   employees
GROUP  BY department_id;

-- 3. Retrieve the entire row with the maximum total_purchase
SELECT *
FROM   customers c
WHERE  total_purchase = (
  SELECT MAX(total_purchase) FROM customers
);

-- 4. Use DISTINCT to avoid duplicate values
SELECT MIN(DISTINCT discount_percent) AS lowest_unique_discount
FROM   promotions;

Expected Output Using SQL MIN AND MAX

Each query returns one row per aggregation scope
Without GROUP BY, the result is a single row containing the minimum or maximum value
With GROUP BY, each group produces a row showing its lowest and highest values
Queries that wrap MIN or MAX in a subquery can be used to fetch the full record(s) that match the extreme value

Use Cases with SQL MIN AND MAX

Quickly identify smallest or largest numeric, date, or text values.
Compute per-group extremes for reporting, such as best and worst sales day per store.
Validate data ranges (e.g., confirm timestamps fall within expected bounds).
Combine with subqueries to pull full records associated with minimum or maximum values.
Support data quality checks by ensuring values never exceed specified limits.

Common Mistakes with SQL MIN AND MAX

Forgetting that MIN and MAX ignore NULL, leading to unexpected results.
Using MIN or MAX in SELECT without GROUP BY alongside other non-aggregated columns, triggering SQL errors.
Assuming DISTINCT changes results when duplicates do not exist.
Expecting MIN or MAX to return multiple rows when the underlying data contains ties; they still return a single value unless used within a subquery to fetch all tying rows.

First Introduced In

SQL-86 (first ISO SQL standard)

Frequently Asked Questions

What data types can MIN and MAX handle?

Any comparable type works: numbers, dates, timestamps, strings, and even boolean values in some dialects.

How do I include NULLs in MIN or MAX calculations?

You cannot directly include NULL. Wrap the column in COALESCE or a CASE expression to replace NULL with a sentinel value first.

How do I fetch rows tied for the minimum value?

Use a subquery: SELECT * FROM table t WHERE column = (SELECT MIN(column) FROM table); This returns every row equal to the global minimum.

Is DISTINCT useful with MIN or MAX?

DISTINCT only matters when duplicates exist in the input set. If duplicates are common, DISTINCT can change the result when using non-numeric collations or specialized data types.