Oracle MIN Function

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

Introduction to Oracle MIN() function #

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

Here’s the syntax of the Oracle MIN() function:

MIN( expression );Code language: SQL (Structured Query Language) (sql)

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

Like the MAX function, the MIN() function ignores NULL.

Oracle MIN() function examples #

We’ll use the products table in the sample database for the demonstration.

Basic Oracle MIN() function example #

The following statement uses the MIN function to return lowest list price of all products:

SELECT
  MIN(list_price)
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

Using Oracle MIN() function in the subquery #

To retrieve the products with the lowest price, you can use the MIN() function in a subquery:

SELECT
  product_id,
  product_name,
  list_price
FROM
  products
WHERE
  list_price = (
    SELECT
      MIN(list_price)
    FROM
      products
  );Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

• First, the subquery returns the lowest price.
• Second, the outer query retrieves the products whose list price is equal to the lowest price.

Using Oracle MIN() with GROUP BY clause #

The following statement uses the MIN() function with a GROUP BY clause to retrieves the lowest list prices by product category:

SELECT
  category_id,
  MIN(list_price)
FROM
  products
GROUP BY
  category_id
ORDER BY
  category_id;Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

• First, the GROUP BY clause first groups rows from 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 set more meaningful, you can retrieve category names instead of the id by joining the products table with the product_categories table:

SELECT
  category_name,
  MIN(list_price)
FROM
  products
  INNER JOIN product_categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

• Use the MIN() function to return the minimum value from a set of values.
• Use the MIN() function with the GROUP BY clause to to return the minimum value for each group.