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
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:
|3||Premium Red Widget||3.99|
|4||Extra Large Orange Widget||4.49|
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.
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?