VMware ESXi and vSphere Cluster Management

SQL MIN() Function: Find the Lowest Value in a Column

Learn how SQL MIN() returns the smallest numeric, date, or text value, with examples using WHERE, GROUP BY, aliases, NULLs, and related aggregates.

MIN() is an SQL aggregate function that returns the smallest value found in a column or expression. It is useful for finding the lowest price, earliest date, or alphabetically first text value.

An aggregate function calculates one result from multiple rows. Without grouping, MIN() normally produces one result for the rows considered by the query.

What MIN() Does

A database table stores records in rows and named fields in columns. For example, a products table might have product_id, product_name, category_id, and amount columns.

When you apply MIN() to the amount column, SQL examines the relevant amounts and returns the lowest one. It returns the value itself, not automatically the complete row containing that value.

SELECT MIN(amount)
FROM products;

This query returns one value: the smallest amount in products.

Basic MIN() Syntax

SELECT MIN(column_name)
FROM table_name;

• column_name is the column whose lowest value you want to find.
• table_name is the table containing that column.
• MIN() is the aggregate function that calculates the result.

You can assign an alias to the result. An alias is a temporary output name for a selected expression or aggregate result.

SELECT MIN(column_name) AS lowest_value
FROM table_name;

The alias makes the result column easier to understand in query output and application code.

Example: Find the Lowest Product Amount

Suppose products contains these amounts:

• 615.45
• 899.99
• 1,250.00
• 725.50

Use this query to find the lowest amount:

SELECT MIN(amount) AS lowest_amount
FROM products;

Expected result:

lowest_amount
-------------
615.45

The result has one row because the query calculates one aggregate across the entire table.

Filtering Rows Before Calculating the Minimum

Use the WHERE clause to limit which rows participate in the calculation. WHERE filters rows before the aggregate result is calculated.

SELECT MIN(amount) AS lowest_amount
FROM products
WHERE category_id = 2;

This query does not find the lowest amount across all products. It first keeps products whose category_id is 2, then finds the smallest amount among those remaining rows.

The same pattern can be used with other conditions:

SELECT MIN(amount) AS lowest_active_amount
FROM products
WHERE amount > 0;

Finding the Minimum for Each Category

To calculate a separate minimum for each category, use GROUP BY. GROUP BY divides rows into groups so an aggregate can be calculated for each group.

SELECT category_id,
       MIN(amount) AS lowest_amount
FROM products
GROUP BY category_id;

The result contains one row per category. Each row includes the category identifier and that category's smallest amount.

When selecting a regular column together with an aggregate, the regular column generally must appear in GROUP BY. Here, category_id is both selected and used to define the groups.

MIN() with Dates and Text

MIN() is commonly used with numeric values, but many SQL systems also support it for dates and text.

Earliest Date

For an orders table with an order_date column, the earliest available order date can be found with:

SELECT MIN(order_date) AS first_order_date
FROM orders;

This returns the earliest date among the rows considered by the query.

Lowest Text Value

For text, the result is usually determined by the database's collation and comparison rules. A collation defines how text is compared, including rules for ordering characters, case, and sometimes accents.

SELECT MIN(product_name) AS first_product_name
FROM products;

Do not assume that text ordering is identical in every database system. Check the collation when exact text ordering matters.

How MIN() Handles NULL

NULL represents a missing or unknown value. Aggregate functions generally ignore NULL values, so a NULL amount does not become the minimum when other non-NULL amounts are available.

For example, if the considered values are 615.45, 899.99, and NULL, the result is 615.45.

If every considered value is NULL, or if no rows match the query, MIN() commonly returns NULL:

SELECT MIN(amount) AS lowest_amount
FROM products
WHERE category_id = 999;

Whether there are no matching rows or only NULL values, inspect the source data before treating the result as a numeric value.

MIN() Compared with Other Aggregate Functions

• MIN() returns the smallest value.
• MAX() returns the largest value.
• SUM() adds numeric values together; it does not select an extreme value.
• AVG() calculates an average of numeric values.
• COUNT() counts rows or values, depending on the expression used.

SELECT MIN(amount) AS lowest_amount,
       MAX(amount) AS highest_amount,
       SUM(amount) AS total_amount
FROM products;

MIN() Returns a Value, Not Automatically the Lowest-Value Row

This query returns only the lowest scalar amount:

SELECT MIN(amount) AS lowest_amount
FROM products;

It does not automatically return the product name or product ID associated with that amount. Selecting an unrelated column alongside MIN() can produce an SQL error or an unreliable result, depending on the database system.

To retrieve complete rows whose amount equals the minimum, one general pattern uses a subquery:

SELECT product_id, product_name, amount
FROM products
WHERE amount = (
    SELECT MIN(amount)
    FROM products
);

This can return more than one row when multiple products share the same minimum amount. Database-specific features may provide other ways to choose one row when ties need to be handled specially.

Common Problems and Troubleshooting

The Query Returns NULL

Common causes include:

• The table has no rows.
• The WHERE condition matched no rows.
• Every value considered by the query is NULL.

Inspect the rows being aggregated:

SELECT amount
FROM products
WHERE category_id = 2;

Check both the filter condition and the values in the selected column.

There Is One Minimum Instead of One per Category

Without GROUP BY, SQL calculates one minimum for the complete filtered input. Add the grouping column when you need a result for every category:

SELECT category_id,
       MIN(amount) AS lowest_amount
FROM products
GROUP BY category_id;

A Nonaggregated Column Causes an Error

A selected column that is not inside an aggregate function generally must be included in GROUP BY.

For example, this may fail:

SELECT category_id, product_name, MIN(amount)
FROM products
GROUP BY category_id;

product_name is neither aggregated nor grouped. Remove it when only the category minimum is needed, or use a row-retrieval pattern such as a subquery when the complete lowest-value record is required.

The Result Is Not the Complete Lowest-Value Record

MIN(amount) returns a single minimum value, not all columns from the row that contains it. Use a subquery or another database-supported row-selection technique to retrieve the associated record.

Exam-Relevant Summary

• MIN() is an aggregate function that returns the smallest value in a column or expression.
• Without GROUP BY, it normally returns one result for the complete filtered input.
• WHERE filters rows before MIN() is calculated.
• GROUP BY produces one minimum per group.
• NULL values are generally ignored, but an all-NULL or empty input commonly produces NULL.
• Use an alias such as AS lowest_amount to give the result a clear name.
• MIN() returns a value, not automatically the full row associated with that value.

For related lessons, see SQL aggregate functions and minimum values.