SQL MIN() and MAX() Functions

The SQL MIN() and MAX() functions are essential aggregate functions in SQL used for data analysis. They allow you to extract the minimum and maximum values from a specified column, respectively, making them invaluable when working with numerical, string, or date-based data.

In this article, we will learn the SQL MIN() and MAX() functions in detail, provide syntax examples, and illustrate how these functions can be applied in different scenarios.

SQL MIN() Function

SQL MIN() function returns the smallest value in the column. It can be used with various data types, including numbers, strings, and dates.

The MIN() function can be used with the DISTINCT keyword to return the minimum value among unique values in a column. It allows for efficiently finding the minimum value in a dataset, making it essential for data manipulation and analysis.

Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SQL MAX() Functions 

SQL MAX() function returns the largest value in the column. It can can be used with various data types, including numbers, strings, and dates.

The MAX() function in SQL can be used in combination with other SQL clauses and functions, such as GROUP BY, HAVING, and subqueries which can be useful for data analysis and reporting

Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

SQL MIN() and MAX() Function Examples

Let's look at some examples of MIN() and MAX() functions in SQL to understand the concept better. We will be using the following table to in the examples. To create this table, write the given SQL queries.

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: SQL MIN() function

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

Query:

SELECT MIN(Age) FROM Customer ;

Output:

Example 2: SQL MAX() function

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

Query:

SELECT Max(Age) FROM Customer;

Output:

Example 3: Using MIN() and MAX() 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() or MAX() Functions In the 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:

Important Points About SQL MIN() and MAX() Functions

• The MIN() function returns the minimum value in a selected column, while the MAX() function returns the maximum value.
• The MIN() and MAX() functions can be used with various data types, including numbers, strings, and dates.
• The MIN() and MAX() functions are considered aggregate functions, as they operate on a set of rows and return a single value.
• The MIN() and MAX() functions can be used in combination with other SQL clauses and functions, such as GROUP BY, HAVING, and subqueries.
• The MIN() and MAX() functions are powerful tools in SQL that are essential for data manipulation and analysis.

Conclusion

The MIN() and MAX() functions are powerful tools for data analysis in SQL, allowing you to extract the lowest and highest values from a dataset. By combining these functions with other SQL clauses like GROUP BY and HAVING, you can perform sophisticated data analysis and gain deeper insights into your data. Whether you're working with numerical data, dates, or strings, these aggregate functions will help you quickly summarize and analyze your data.