Skip to main content

SQL MIN Function

Introduction​

The SQL MIN function is one of the fundamental aggregate functions that allows you to find the smallest value in a column. It's an essential tool when you need to analyze data and identify minimum values across your datasets.

In this tutorial, you'll learn:

  • What the MIN function does and how it works
  • The syntax for using MIN
  • How to combine MIN with other SQL features
  • Real-world applications of the MIN function

What is the MIN Function?​

The MIN function is an aggregate function in SQL that returns the smallest value from a selected column. It analyzes all non-NULL values in the specified column and determines which one is the minimum.

Basic Syntax​

The basic syntax for the MIN function is:

sql
SELECT MIN(column_name) FROM table_name;

Where:

  • column_name is the name of the column you want to find the minimum value for
  • table_name is the name of the table containing the data

Simple Examples​

Example 1: Finding the Lowest Price​

Let's say we have a table called products with product information:

sql
SELECT MIN(price) AS lowest_price FROM products;

Input (products table):

product_idproduct_nameprice
1Laptop899
2Smartphone499
3Headphones79
4Tablet349

Output:

lowest_price
79

This query returns 79, which is the lowest price in the products table.

Example 2: Finding the Earliest Date​

Let's find the earliest order date from an orders table:

sql
SELECT MIN(order_date) AS earliest_order FROM orders;

Input (orders table):

order_idcustomer_idorder_datetotal_amount
100152023-03-15150.00
100282023-02-2875.50
1003122023-04-02210.25
100452023-03-2045.00

Output:

earliest_order
2023-02-28

The query returns February 28, 2023, as the earliest order date.

Using MIN with WHERE Clause​

You can combine the MIN function with the WHERE clause to find the minimum value based on certain conditions.

sql
SELECT MIN(price) AS lowest_electronic_price 
FROM products 
WHERE category = 'Electronics';

This will find the lowest price only for products in the Electronics category.

Using MIN with GROUP BY​

The MIN function becomes more powerful when combined with the GROUP BY clause. This allows you to find the minimum value for each group of data.

Example: Finding the Lowest Price per Category​

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

Input (products table with category):

product_idproduct_namepricecategory
1Laptop899Electronics
2Smartphone499Electronics
3Desk Lamp35Home
4Coffee Mug12Home
5T-Shirt25Clothing
6Jeans45Clothing

Output:

categorylowest_price
Electronics499
Home12
Clothing25

This query returns the minimum price for each product category.

Using MIN with HAVING Clause​

You can use the HAVING clause with MIN to filter group results:

sql
SELECT category, MIN(price) AS lowest_price 
FROM products 
GROUP BY category
HAVING MIN(price) < 30;

Output:

categorylowest_price
Home12
Clothing25

This query only shows categories where the minimum price is less than 30.

MIN with Subqueries​

You can use the MIN function in subqueries to compare values or filter data:

sql
SELECT product_name, price 
FROM products 
WHERE price = (SELECT MIN(price) FROM products);

This query finds all products that have the lowest price in the entire table.

Output:

product_nameprice
Coffee Mug12

Real-World Applications​

1. Finding the Youngest or Oldest Person​

sql
-- Finding the oldest employee
SELECT MIN(birth_date) AS oldest_employee_birthdate 
FROM employees;

-- Finding the youngest employee
SELECT MAX(birth_date) AS youngest_employee_birthdate 
FROM employees;

2. Identifying Lowest Performing Sales Regions​

sql
SELECT region, MIN(sales_amount) AS lowest_sale 
FROM sales 
GROUP BY region;

3. Finding the First Customer to Sign Up​

sql
SELECT MIN(signup_date) AS first_signup 
FROM customers;

4. Identifying Minimum Inventory Levels​

sql
SELECT product_id, product_name, stock_quantity 
FROM inventory 
WHERE stock_quantity = (SELECT MIN(stock_quantity) FROM inventory);

Important Notes About MIN​

  1. NULL Values: The MIN function ignores NULL values. If all values in a column are NULL, the result will be NULL.

  2. Data Types: MIN works with various data types including:

    • Numeric values (finding the smallest number)
    • Text (finding the first value alphabetically)
    • Dates (finding the earliest date)

  3. Performance: For large datasets, ensure your columns are properly indexed for better performance when using the MIN function.

Visualizing MIN Function​

Here's a simple diagram showing how the MIN function works:

Common Errors and Troubleshooting​

  1. Empty Result Set: If your query returns NULL, check if your table is empty or if all values in the column are NULL.

  2. Wrong Data Type: Ensure you're using MIN on a column with the appropriate data type for your comparison.

  3. Performance Issues: If your MIN query is slow, consider adding an index to the column you're analyzing.

Practice Exercises​

  1. Create a table named students with columns for student_id, name, age, and score. Insert some sample data and write a query to find the youngest student.

  2. Write a query to find the lowest temperature recorded for each city in a weather table.

  3. Create a query that shows products with a price equal to the minimum price in their respective categories.

Summary​

The SQL MIN function is a powerful tool for finding the smallest value in a dataset. It can be used on its own or combined with other SQL features like WHERE, GROUP BY, and HAVING to extract valuable insights from your data.

When using the MIN function:

  • Remember it ignores NULL values
  • It works with numeric, text, and date data types
  • It can be combined with GROUP BY to find minimums within categories
  • It's often used in subqueries for comparison operations

Additional Resources​


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)