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.
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)
B) Oracle MIN() in the subquery
To get more information on the cheapest product whose 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)
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)
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 the 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)
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)
In this tutorial, you have learned how to use the Oracle MIN()
function to return the minimum value from a set of values.