Oracle MIN

Summary: in this tutorial, you will learn how to use the Oracle MIN() function to return the minimum value from a set of values.

Oracle MIN() function syntax

The Oracle MIN() function is an aggregate function that returns the minimum value of a set.

The syntax of the Oracle MIN() function is as follows:

MIN( expression );

Unlike other aggregation functions such as AVG() and SUM(), the DISTINCT and ALL clauses are irrelevant to the MIN() function.

The Oracle MIN() function ignores NULL values as well.

Oracle MIN() function examples

We will use the products table in the sample database for the demonstration.

A) Simple Oracle MIN() function example

The following example returns the cheapest product list price:

SELECT
    MIN( list_price )
FROM
    products;

B) Oracle MIN() in subquery

To get more information on the cheapest product whose the list price is the lowest, you use the following statement:

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

In this example, the subquery returns the lowest price. And the outer query retrieves the products whose list price is equal to the lowest price.

C) Oracle MIN() with GROUP BY clause

The following statement retrieves the lowest list prices by product category.

SELECT
    category_id,
    MIN( list_price )
FROM
    products
GROUP BY
    category_id
ORDER BY
    category_id;

In this example, the GROUP BY clause first divides the rows in the products table by product category into groups. Then, the MIN() function returns the lowest list price of products for each group.

To make the result of the query more meaningful, you can get the product category name instead of category id. To do it, you join the products table with the product_categories table as follows:

SELECT
    category_name,
    MIN( list_price )
FROM
    products
INNER JOIN product_categories
        USING(category_id)
GROUP BY
    category_name
ORDER BY
    category_name;

D) Oracle MIN() function with HAVING clause

To filter groups returned by the GROUP BY clause and the MIN() function, you use a HAVING clause.

For example, to get the product category whose lowest list price of products is greater than 500, you use the following query:

SELECT
    category_name,
    MIN( list_price )
FROM
    products
INNER JOIN product_categories
        USING(category_id)
GROUP BY
    category_name
HAVING
    MIN( list_price )> 500
ORDER BY
    category_name;

In this tutorial, you have learned how to use the Oracle MIN() function to return the minimum value from a set of values.