Finding the minimum in SQL is easy: just use the MIN() aggregate function. In this article, you will learn how to do it in different real-case scenarios.

What is the Minimum Function in SQL?

The minimum function or MIN() is an SQL aggregate function that outputs the smallest value in a column. What is considered ‘smallest’ depends on the type of data:

• Numeric data: The lowest value
• Textual data: Legixocraphically smallest value, i.e., alphabetically; it’s case-sensitive
• Date and time data: The earliest (oldest) date or time
• Boolean data: FALSE (or 0) is smaller than TRUE (or 1)

Another vital thing to note is that MIN() ignores NULL values when calculating the minimum.

Why Finding the Minimum Value is Important in SQL

Identifying the minimum value in a dataset has many important applications in data analysis and decision-making.

1. Data Insights & Decision-Making: MIN() helps understand data patterns, e.g., the month with the lowest sales figures or the lowest web traffic. It can also help you in allocating resources, such as reducing costs in production or logistics.
2. Ranking: In certain scenarios, the lowest value represents the best value (e.g., fastest time, lowest price), so it can be used as a benchmark for excellence.
3. Error Detection & Quality Control: By finding the lowest values in the dataset, you can more easily find outliers, anomalies, and even errors (e.g., negative or unreasonably low values)
4. Time-Series Analysis: In analyzing time series, MIN() allows you to identify starting points, e.g., first transaction or registration.
5. Setting Baselines: You can establish baselines by using MIN() to define the lower bound for ranges, e.g., in dynamic pricing or grading systems.

Basic Syntax for Finding Minimum in SQL

SQL MIN() has a straightforward syntax, just like other SQL aggregate functions.

SELECT MIN(column_name) AS column_alias
FROM table_name;

The approach is to write the column name in the function parentheses for which you wish to find the minimum value.

Let’s now see how to apply this general syntax to different use cases. For each use case, I’ll first show a generic example. Then, we’ll see how to apply the logic to solving an interview question from StrataScratch.

Example 1: Finding the Minimum Value in a Single Column

The simplest way to use MIN() is to find a minimum value in a single column, which the syntax above shows.

As an example, here’s a table named products.

Question: Find the cheapest product in the table.

Solution: In the SQL MIN() function, write the price column, which is the column from which you want the lowest value. In the FROM clause, reference the table products.

SELECT MIN(price) AS lowest_price
FROM products;

Output: The lowest price is 23.

Let’s now see a scenario from one of the actual interview questions.

Interview Question Example

Question: ESPN's interview question asks you to find the athletes' lowest, average, and highest ages across all Olympics. It simplifies things by advising us to treat an athlete who participated in more than one discipline at one Olympic games as a separate athlete.

Link to the question: https://platform.stratascratch.com/coding/9943-winter-olympics-events-list-by-height

Dataset: The question provides the olympics_athletes_events table.

Solution & Output: The column used in all the aggregate functions is age. First, let’s find the lowest age: write the column name in the MIN() function in SQL.

Then, we need to find the average and highest age; we’ll do this using the AVG() and MAX() functions, respectively. Using AVG() and MAX() in SQL is the same as MIN(). We only change the function name.

SELECT MIN(age) AS lowest_age,
       AVG(age) AS mean_age,
       MAX(age) AS highest_age 
FROM olympics_athletes_events;

Here’s the output.

Example 2: Finding the Minimum in SQL with GROUP BY

Using MIN() with GROUP BY means you can list other columns in SELECT besides the aggregated column, i.e., the column with the MIN() calculation. That way, you label the minimum values and show additional information about them.

Take this example where we again use the table products.

Question: Find the cheapest product by product category.

Solution: In this query, we list the product_category in SELECT, then add the calculation of the lowest price using MIN().

We must group the data by all the columns appearing in SELECT (except the aggregate columns); that’s the rule for using GROUP BY. In our case, there’s only the product_category column.

SELECT product_category,
	 MIN(price) AS lowest_price
FROM products
GROUP BY product_category;

Output: The query above is all you need to display the lowest product prices per product category.

Interview Question Example

Question: Here’s an Airbnb interview question.

Link to the question: https://platform.stratascratch.com/coding/9625-cheapest-properties

The question asks you to find the price of the cheapest property for every city.

Dataset: We’ll be working with the airbnb_search_details table.

Solution & Output: The approach is the same as in the generic example we’ve shown above. As we’re looking for the minimum price by city, we list the column city in SELECT. Then, we write the column price in the MIN() function and give the column the min_price alias.

SELECT city,
       MIN(price) AS min_price
FROM airbnb_search_details
GROUP BY city;

The output shows each city and the cheapest property price in that city.

Example 3: Using Minimum with Multiple Conditions (WHERE Clause)

You can narrow down your query results further by combining MIN() with the multiple conditions in WHERE.

Let’s go back to our generic example with the table products.

Question: Find the lowest price for the items in the ‘Electronics’ category that are more expensive than $500.

Solution: Nothing changes when using the MIN() function; it’s the same as in the previous examples. All the narrowing down of data is done in the WHERE clause.

The first condition is that the product category is ‘Electronic’. We add another condition – the price must be above 500 – using the AND keyword.

SELECT MIN(price) AS lowest_price
FROM products
WHERE product_category = 'Electronics' AND price > 500;

Output: The output shows that the lowest-priced electronics item over $500 is $800.

Interview Question Example

Question: An interview question by the City of San Francisco asks you to find the first and the last inspections per municipality for vermin infestations. The output should also contain the business postal code.

Link to the question: https://platform.stratascratch.com/coding/9732-find-the-first-and-last-inspection-dates-for-vermin-infestations-per-municipality

Dataset: We’ll work with the table sf_restaurant_health_violations.

Solution & Output: We’ll use the SQL MIN() and MAX() aggregate functions to solve this problem. The logic is this: MIN() returns the oldest date, which is the first inspection date, while MAX() returns the latest date, which is the last inspection date.

The column we’re using in both functions is inspection_date. We also list the business_postal_code column in SELECT; the question asks for that.

We then use WHERE to filter data. The first condition is that the inspections are for vermin infestations. As we don’t have the exact name of such an inspection, we use ILIKE (case-insensitive) and the % wildcard to look for the string 'vermin' anywhere in the strings in the violation_description column.

The question does not require the second condition in WHERE, but we’ll add it to cover the edge case. We’re ensuring that only data where the business postal code is available (i.e., is not NULL) is shown in the output.

Finally, we group the output by the postal code.

SELECT business_postal_code,
       MIN(inspection_date) AS first_inspection,
       MAX(inspection_date) AS last_inspection
FROM sf_restaurant_health_violations
WHERE violation_description ILIKE '%vermin%' AND business_postal_code IS NOT NULL
GROUP BY business_postal_code;

Here’s the output.

Example 4: Using Minimum in SQL with JOINs

You’re not limited to finding the minimum values only in one table; MIN() can also be used in queries with JOIN in SQL. In case you don’t know, JOIN is used to combine data from two or more tables.

Let me show you a quick example. You have two tables: customers and orders.

The customers table looks like this.

The orders table is this.

Question: Find the lowest order value by the customer.

Solution: Nothing changes in the way we’re using MIN(). The only thing is that, since we’re using data from two tables, we put the name of the table (or an alias, in this case) in front of the column name. This is for two reasons: one, the code is more readable, and second, the database will know which column we’re referring to if there are columns named the same in both tables.

Now, to access data from both tables, we need to join them. In FROM, we reference the table orders and give it an alias o. In JOIN, we reference the table customers and assign the alias c. The tables are joined on the common column, which is customer_id from orders and id from customers.

Finally, like when using SQL MIN() with only one table, all the non-aggregated columns appearing in SELECT must be listed in GROUP BY.

SELECT c.first_name,
       c.last_name,
	 MIN (o.amount) AS minimum_order
FROM orders o
JOIN customers c
ON o.customer_id = c.id
GROUP BY c.first_name, c.last_name;

Output: The query output is shown below.

Let’s apply what we’ve learned here to a real case scenario.

Interview Question Example

Question: The question by Noom wants you to find the minimum, average, and maximum number of days it takes to process a refund for accounts opened on 1 January 2019 or later. The output should be grouped by billing cycle in months.

Link to the question: https://platform.stratascratch.com/coding/2125-process-a-refund

Dataset: We’ll be working with three tables, the first one being noom_signups.

The second table is noom_transactions.

The third table is noom_plans.

Solution & Output: Let’s start the code explanations with JOINs. We have to JOIN three tables, which is done exactly the same way as with two tables; we only add another JOIN to form a kind of ‘chain of joins’.

So, we reference the table noom_transactions in FROM and give it an alias t. Then we JOIN it with the table noom_signups on the common column signup_id. Next, we add another JOIN, which references the third table, noom_plans, and joins it with the second (noom_signups) table on the plan_id column. We now have access to all data from all tables.

We can proceed with writing the calculation part of the code. The output should be grouped by billing cycle in months, so we list that column from the table noom_plans in SELECT. The time for refund processing is calculated by subtracting the settled_at from the refunded_at column. We write this subtraction in MIN(), AVG(), and MAX() to calculate the minimum, average, and maximum number of days for getting a refund.

Now, the final touches. The calculation should be performed only on accounts opened on 1 January 2019 or later. We exclude any older accounts by writing the condition in WHERE. Finally, we group the output so we get all the calculations by the billing cycle in months.

It’s not required, but let’s make the output more readable by sorting it by billing cycles using ORDER BY.

SELECT p.billing_cycle_in_months,
       MIN(t.refunded_at - t.settled_at) AS min_days,
       AVG(t.refunded_at - t.settled_at) AS avg_days,
       MAX(t.refunded_at - t.settled_at) AS max_days
FROM noom_transactions t
JOIN noom_signups s ON t.signup_id = s.signup_id
JOIN noom_plans p ON s.plan_id = p.plan_id
WHERE s.started_at >= '2019-01-01'
GROUP BY p.billing_cycle_in_months
ORDER BY p.billing_cycle_in_months  ASC;

The output is shown here.

Example 5: Using Minimum in SQL in HAVING

Another use case of minimum in SQL is using it in HAVING to filter data after the aggregation.

Here’s a generic example that uses tables customers and orders, like in the previous example.

Question: Find customers whose minimum order across all their orders is equal to or greater than $250. Output customer names and the lowest order value.

Solution: The solution is almost identical to the previous generic example. The only difference is we now have a filtering condition in HAVING. The logic of the solution is to first find the minimum order across all orders for each customer. Then, exclude customers whose minimum order is below 250 from the output.

This exclusion or filtering is done in HAVING. It’s a clause for filtering outputs after aggregation, allowing aggregate functions. We write the MIN() calculation the same way as in SELECT and then set the criteria that this minimum has to be 250 or higher.

SELECT c.first_name,
       c.last_name,
	 MIN (o.amount) AS minimum_order
FROM orders o
JOIN customers c
ON o.customer_id = c.id
GROUP BY c.first_name, c.last_name
HAVING MIN (o.amount) >= 250;

Output: The query returns these two rows.

You could be tempted to think that the same result could be achieved by simply excluding all the orders below 250 in WHERE before you calculate the minimum.

SELECT c.first_name,
       c.last_name,
	   MIN (o.amount) AS minimum_order
FROM orders o
JOIN customers c
ON o.customer_id = c.id
WHERE o.amount >= 250
GROUP BY c.first_name, c.last_name;

Not quite, as the output has three rows; it includes data about Frank Zappa. Why? Because you excluded his lowest order (which is $200) before aggregation, thus artificially making $500 its ‘new’ lowest order, so it’s displayed in the output. Beware of the distinction between filtering before and after aggregation!

Interview Question Example

Question: An ESPN interview question wants us to find the average weight of medal-winning judo players of each team, where the minimum age within the team is at least 20 and the maximum age is at most 30.

Link to the question: https://platform.stratascratch.com/coding/10144-average-weight-of-medal-winning-judo

Dataset: The question gives you the table olympics_athletes_events to work with.

Solution & Output: We calculate the average weight per team by using the AVG() function and listing the column team in SELECT and GROUP BY.

The calculation has to be for judo, so we include this condition in WHERE. In addition, the calculation should be only for judo medal-winners, so the column medal shouldn’t be NULL; this is another condition in WHERE.

The output should include data only for those teams where the minimum age is 20 or more, and the maximum age is at most 30. This calls for filtering after the aggregation, i.e., we use HAVING. In HAVING,  there’s a MIN() calculation as the first filtering condition and a MAX() calculation as the second filtering condition.

SELECT team,
       AVG(weight) AS average_player_weight
FROM olympics_athletes_events
WHERE sport = 'Judo'AND medal IS NOT NULL
GROUP BY team
HAVING MIN(age) >= 20 AND MAX(age) <= 30;

Here’s the output.

Alternative Approach: Using Subqueries to Find the Minimum Value in SQL

Using subqueries to find the minimum value is an alternative approach, often used in more complex scenarios, especially when the result of one query is needed as input for another.

Take, for example, the generic example we showed for using MIN() with JOIN. This is the query we wrote.

SELECT c.first_name,
       c.last_name,
	 MIN (o.amount) AS minimum_order
FROM orders o
JOIN customers c
ON o.customer_id = c.id
GROUP BY c.first_name, c.last_name;

We can easily rewrite it with a subquery and avoid using JOIN.

SELECT c.first_name,
       c.last_name,
       (SELECT MIN(o.amount)
        FROM orders o
        WHERE o.customer_id = c.id) AS minimum_order
FROM customers c;

The outer query selects the customers’ first and last names from the table customers. However, the column minimum_order is now a subquery that calculates the minimum order value.

In that subquery, you write a simple SELECT that calculates the minimum based on the column amount from the table orders. The ‘joining’ of the two tables is done in WHERE; we match the customer IDs from the subquery with the customer IDs from the outer query.

The output is the same as that of JOIN.

Common Pitfalls When Using Minimum in SQL

You see, the SQL MIN() function is quite simple to use. However, there are some pitfalls to watch out for when using it.

1. Forgetting the GROUP BY Clause: Whenever you have non-aggregated data along with MIN() in SELECT, don’t forget to include the GROUP BY clause. It must contain all the non-aggregated columns from SELECT. Otherwise, the code won’t work.
2. Mishandling NULLs: You’ve got to keep in mind that MIN() ignores NULL values. If you need to treat NULLs as the lowest value, handle NULLs explicitly by using SQL COALESCE() function and replacing NULLs with 0s.
3. Using MIN() in WHERE: The WHERE clause doesn’t accept aggregate values, including MIN(). Use HAVING to filter the results after aggregation.

Conclusion

There you have it: the MIN() aggregate function’s magical simplicity for finding the minimum values in SQL. You learned its basic syntax and why you should use it. Then, we dealt with where and how you can use it by showing five use-case scenarios consisting of generic examples and real interview questions from our platform.

Solving those SQL interview questions always tests your understanding of how to apply what you know about MIN() into practice. And practice is all you need to become good at this, so choose some of our analytical coding questions and keep practicing.