How to Use The SQL MIN Function

The SQL MIN function is a useful tool for returning the minimum value within a specific column of a table. It is commonly used in SELECT statements to retrieve the lowest value from a range of values, or to find the lowest value within a set of records.

To use the MIN() function, you simply need to specify the name of the column that you want to retrieve the minimum value from. For example:

SELECT MIN(transaction_amount)
FROM sales_records;

You can also use the SQL MIN function in combination with other clauses to further filter and refine your results. For example, you could use the WHERE clause to only return the minimum value from a specific group of records. You could use the GROUP BY clause to find the minimum value for each group within your table.

Using GROUP BY With SQL MIN

If you want to include other columns in your SELECT statement, you will need to use the GROUP BY clause. Each column not included in an aggregate function must be included in the GROUP BY clause.

Let’s say that we want to see the minimum transaction amount by customer in our table. We’ll include customer_name in our SELECT statement. We will also need to include it in our GROUP BY clause.

SELECT customer_name, MIN(transaction_amount)
FROM sales_records
GROUP BY customer_name;

This will give us one row for each customer name and the lowest transaction_amount found for that customer.

Using the HAVING Clause

You can filter your query results based on the SQL MIN function using the HAVING clause. In this case, you would specify the HAVING clause and the criteria to filter with, including the aggregate function. Here is an example:

SELECT customer_name, MIN(transaction_amount)
FROM sales_records
GROUP BY customer_name
HAVING MIN(transaction_amount) > 30;

This query will give us a record for each customer where the minimum transaction_amount is greater than 30. You should know that you can combine HAVING and WHERE clauses within the same query. The WHERE clause will be applied first and then the HAVING clause will apply after that.

SELECT customer_name, MIN(transaction_amount)
FROM sales_records
WHERE upper(customer_name) LIKE '%R%'
GROUP BY customer_name
HAVING MIN(transaction_amount) > 30;

This query will find all the records where the customer_name has an “R” in it. It will then filter those to show only those records where the transaction_amount is greater than 30.

Using SQL MIN in a Subquery

You can use the SQL MIN function in a subquery as well. Let’s say we want to find all of the products that share the lowest price. Our table might look like this:

In this case, the Green Widget and Blue Widget are our two lowest priced products. The following query will get us all the products that are priced at the lowest unit price in the table.

SELECT *
FROM products
WHERE unit_price = (SELECT MIN(unit_price) FROM products);

If we add products at the same price in the future, they will show up in this query. On the other hand, if we introduce products at a lower cost – for example, $1.99 – then only those products will show in our query results.

When we use the SQL MIN function in a subquery, we do not need to apply GROUP BY or HAVING clauses. The database will perform the subquery first and use the results from that query within our main query.

Overall, the SQL MIN function is a simple yet powerful tool for finding the minimum value within a column of a table. Whether you are working with numerical data or character strings, the MIN function can help you quickly and easily find the lowest value within your data set.