SQL MIN

The MIN() function in SQL is used to retrieve the minimum value from a specified column in a table. It can be used on numerical, string, or date columns to find the smallest value in the dataset.

 

Syntax of SQL MIN

SELECT MIN(column_name) FROM table_name WHERE condition;

• MIN(): The aggregate function used to find the minimum value.
• column_name: The name of the column from which to find the minimum value.
• table_name: The name of the table where the data is stored.
• condition: Optional condition to filter rows.

 

Key Points

• MIN() works on numerical, date, and string data types.
• It will ignore NULL values.
• The result will return a single value, representing the minimum value in the column.

 

Example of SQL MIN

Consider the following table:

Table: Products

To find the lowest price from the price column:

SELECT MIN(price) AS min_price FROM products;

Output:

Explanation:

• The MIN(price) function returns the lowest value from the price column, which is 1.2 (for Banana).

 

SQL MIN with Conditions

You can apply a condition to find the minimum value based on a specific criterion. For example, to find the lowest price of products that cost more than 2.0:

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

Output:

Explanation:

• The MIN(price) function returns the lowest price from the price column where the value is greater than 2.0.

 

SQL MIN with Grouping

You can also use MIN() with the GROUP BY clause to get the minimum value for each group. For example, to find the lowest price for each product category (assuming a category column):

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

Output:

Explanation:

• The MIN(price) function finds the lowest price for each category. In this example, there is only one category, so the result shows the lowest price overall.

 

SQL MIN with DISTINCT

You can use the DISTINCT keyword with MIN() to find the minimum distinct value in a column. However, in most cases, MIN() does not need DISTINCT because it already operates on all unique values in the column.

For example, to find the lowest distinct price:

SELECT MIN(DISTINCT price) AS min_distinct_price FROM products;

Output:

Explanation:

• The MIN(DISTINCT price) finds the lowest value of distinct prices. In this case, it’s the same result as MIN(price) because all values in the price column are distinct.

 

Summary

The MIN() function in SQL is used to find the minimum value in a specified column. It works on numerical, string, and date columns, and it ignores NULL values. The function can be used with conditions and GROUP BY clauses to refine the query and get the minimum values under different criteria.