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 );
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.

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.

products table

A) Simple Oracle MIN() function example

The following example returns the cheapest product list price:

SELECT MIN( list_price ) FROM products;
Code language: SQL (Structured Query Language) (sql)
Oracle MIN - lowest list price

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

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;
Code language: SQL (Structured Query Language) (sql)
Oracle MIN - GROUP BY example

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;
Code language: SQL (Structured Query Language) (sql)
Oracle MIN - Inner Join example

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

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

Was this tutorial helpful?