SQL MIN() and MAX() Functions

The SQL MIN() and MAX() functions are essential aggregate functions used to find the smallest and largest values in a column. They work with various data types such as numbers, strings, and dates, making them powerful tools for data analysis, filtering, and reporting.

SQL MIN() Function

The MIN() function returns the smallest value in a specified column.

• Works with numeric, string, and date data types.
• Can be used with DISTINCT to find the smallest among unique values.
• Useful for identifying minimum prices, earliest dates, youngest age, etc.

Syntax:

SELECT MIN(column_name) FROM table_name WHERE condition;

SQL MAX() Functions 

The MAX() function returns the largest value in a specified column.

• Works with numeric, string, and date data types.
• Can be combined with GROUP BY, HAVING, and subqueries for advanced analysis.
• Useful for identifying highest salaries, latest dates, oldest age, etc.

Syntax:

SELECT MAX(column_name) FROM table_name WHERE condition;

Examples of MIN() and MAX() Functions

Let's look at some examples of MIN() and MAX() functions in SQL to understand the concept better. We will use the following table for demonstration:

CREATE TABLE Customer( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), LastName VARCHAR(50), Country VARCHAR(50), Age int(2), Phone int(10) ); -- Insert some sample data into the Customers table INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone) VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'), (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'), (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'), (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'), (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Example 1: Finding the Minimum Age

In this MIN() function example, we will fetch the details of customer with minimum age:

Query:

SELECT MIN(Age) AS Min_Age
FROM Customer;

Output:

Example 2: Finding the Maximum Age

In this MAX() function example, we will find customer details with the highest Age.

Query:

SELECT MAX(Age) AS Max_Age
FROM Customer;

Output:

Example 3: MIN() with Other Columns

Suppose we want to fetch a person's name with min age as column min_age then we can use the following query.

Query:

SELECT CustomerName, MIN(Age) AS min_age FROM Customer;

Output:

Example 4: Using MIN() and MAX() with HAVING Clause

Suppose we want to fetch a person's name with max age as column max_age then we can use the following query but with some conditions like min age at least 22

Query:

SELECT CustomerName, MAX(Age) AS max_age
FROM Customer
HAVING MIN(Age) > 22;

Output:

Key Points to Remember

• MIN() → Finds the smallest value.
• MAX() → Finds the largest value.
• Both functions ignore NULL values.
• Can be combined with WHERE, GROUP BY, HAVING for complex queries.
• Works with numeric, string, and date columns.