MIN is probably one of the most common aggregate functions that allows us to pass a set of values and it can return a single value from the provided dataset.
In this tutorial, we will explore the functionality of the min() function in SQL databases such as MySQL. We will use the Sakila sample database for demonstration. However, feel free to use any dataset that you find appropriate.
SQL Min() Function
As we mentioned, this function allows us to pass a set of values and it returns the smallest value from the provided set.
We can express the function syntax as follows:
FROM table_name
WHERE condition;
In this syntax, we call the min() function and pass the column name whose minimum value we wish to retrieve. Since the function is an aggregate function, it computes the min value from all the provided values and returns a single value.
We also specify an optional clause using the WHERE clause to filter the rows based on a specific condition before locating the minimum value.
Examples:
To better understand how to use the min() function in SQL, let us look at a more practical usage of the function.
Basic Usage
Consider the film table from the Sakila sample database. Suppose we wish to find the minimum rental rate for a film.
We can use the min() function as shown in the following:
FROM film;
In this example, we locate the smallest value in the “rental_rate” column of the film table. This should return the lowest rental value among all films in the database.
Resulting Output:
---------------+
0.99|
Advanced Usage
Let us look at a more advanced usage of the function. Suppose we want to find the minimum rental rate for a specific film. For example, “ACE GOLDFIGER”. We can add the WHERE clause to our query as shown in the following:
FROM film
WHERE film_id = (
SELECT film_id
FROM film
WHERE title = 'CITY OF ANGELS'
);
This should return the rental rate for the specified film.
Conclusion
In this tutorial, we learned how to work and use the min() function in SQL. We started with the basics and progressed to the advanced examples where we add the filters using the WHERE clause for a more granular control.
