Previous
Written by: rJTutorial Team | Last Updated:

SQL MIN

The MIN() function is one of the most frequently used aggregate functions in SQL. Its primary purpose is to scan a specific column and return the smallest (minimum) value found within that dataset. Whether you are looking for the cheapest product in an inventory, the earliest hire date in an employee directory, or the lowest temperature recorded in a weather database, MIN() is the tool for the job.

Developer Tip: Since MIN() is an aggregate function, it is highly efficient when used on indexed columns. If you frequently need to find the minimum or maximum of a large dataset, ensure that column is indexed to avoid full table scans.

 

Syntax of SQL MIN

To use the MIN() function, you include it in your SELECT statement. It is a best practice to use an alias (using the AS keyword) so the resulting column has a meaningful name in your application's code.

SELECT MIN(column_name) AS alias_name 
FROM table_name 
WHERE condition;
  • MIN(column_name): The aggregate function that targets the column you want to evaluate.
  • AS alias_name: Renames the resulting column for better readability (e.g., lowest_price).
  • FROM table_name: Specifies the source table.
  • WHERE condition: An optional clause to filter the rows before the minimum value is calculated.
Best Practice: Always use the AS keyword to provide a descriptive name for your result. Without it, many database systems will return a column name like "MIN(price)" or "computed_0," which is harder to work with in your backend code.

 

Key Points

  • Data Types: MIN() isn't just for numbers. It works on Numeric types (lowest number), Date/Time types (earliest date), and String types (first value alphabetically).
  • NULL Values: The function automatically ignores NULL values. It only compares actual data.
  • Single Row Output: Unless you are using a GROUP BY clause, MIN() will always return exactly one row.
Watch Out: If the column you are querying contains only NULL values, the MIN() function will return NULL. Always ensure your application logic can handle a potential null result.

 

Example of SQL MIN

Let's look at a practical example using a product inventory. This table tracks different items and their current market prices.

Table: Products

product_id product_name price
1 Apple 2.5
2 Banana 1.2
3 Cherry 3.0
4 Mango 2.8
5 Orange 1.5

To find the most affordable item in our list, we query the price column:

SELECT MIN(price) AS min_price FROM products;

Output:

min_price
1.2

Explanation:

  • The database engine scans the price column. It compares 2.5, 1.2, 3.0, 2.8, and 1.5. Since 1.2 is the smallest numerical value, it is returned as the result.
Common Mistake: A common mistake is trying to select the product name alongside the minimum price like this: SELECT product_name, MIN(price) FROM products;. In most SQL dialects (like PostgreSQL or SQL Server), this will cause an error because product_name is not part of an aggregate function or a GROUP BY clause.

 

SQL MIN with Conditions

In the real world, you often need to find the minimum value within a specific subset of data. For instance, you might want to find the lowest price among "premium" products (those costing more than $2.00).

SELECT MIN(price) AS min_price_above_2 
FROM products 
WHERE price > 2.0;

Output:

min_price_above_2
2.5

Explanation:

  • The WHERE clause filters out Banana (1.2) and Orange (1.5). Among the remaining items (Apple, Cherry, Mango), the lowest price is 2.5.

 

SQL MIN with Grouping

The MIN() function becomes incredibly powerful when paired with GROUP BY. This allows you to find the minimum value for every category in your table at once. Imagine our products had a category column:

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

Output:

category min_price
Fruit 1.2
Vegetables 0.95

Explanation:

  • The database groups all rows by their category and then calculates the MIN(price) for each specific group independently.

 

SQL MIN with DISTINCT

You can use the DISTINCT keyword inside the MIN() function, although it is rarely necessary. DISTINCT tells SQL to only look at unique values before finding the minimum.

SELECT MIN(DISTINCT price) AS min_distinct_price FROM products;

Output:

min_distinct_price
1.2

Explanation:

  • Logic-wise, the minimum value of a set is the same whether you include duplicates or not. For example, MIN(1, 1, 2, 3) is 1, and MIN(DISTINCT 1, 1, 2, 3) is also 1. Therefore, DISTINCT is seldom used with MIN().

 

Summary

The MIN() function is an essential tool for data analysis in SQL. It is used to find the "floor" of your data the smallest number, the earliest date, or the first string alphabetically. To get the most out of it, remember to use aliases for clarity, leverage GROUP BY for categorical reports, and be mindful of how your database handles NULL values during aggregation.

Previous