Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Aggregate Functions / Oracle MIN

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.

products table

A) Simple Oracle MIN() function example

The following example returns the cheapest product list price:

SELECT MIN( list_price ) FROM products;
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 );
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;
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;
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;
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?
  • YesNo
Previous Oracle SUM
Next Oracle LISTAGG

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Aggregate Functions

  • Oracle Aggregate Functions
  • AVG
  • COUNT
  • MAX
  • MIN
  • LISTAGG
  • SUM

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.