SQL MIN() and MAX() Functions

The MIN() and MAX() functions are commonly used to quickly find the smallest and largest values in a set of data. These functions help in making decisions by identifying the best or worst performance, the lowest price, or the highest temperature. They are useful in various real-world situations where quick comparisons are needed to understand key data points. People can quickly and easily summarize a lot of information by using MIN() and MAX(). In this blog, we will explore the MIN() and MAX() functions in detail with examples.

Table of Contents:

What are MIN() and MAX() Functions in SQL?
Why do we need MIN() and MAX() Functions in SQL?
Syntax of MIN() and MAX() Functions in SQL
Examples of MIN() and MAX() Functions in SQL
Real-world Example
Common Errors to Avoid
Best Practices
Conclusion

What are MIN() and MAX() Functions in SQL?

In MySQL, the MAX() function is an aggregate function that returns the largest (maximum) value from a specified column. The MIN() function returns the smallest (minimum) value from a specified column. These functions are frequently used with the GROUP BY clause to determine the minimum or maximum values within categories.

Why do we need MIN() and MAX() Functions in SQL?

Summary of Data: These functions efficiently scan data to find minimum and maximum values without manually sorting or comparing each record.
Decision Making: Finding extreme values (e.g., lowest salary, highest sale) helps organisations make data-driven decisions.
Performance: It is less computational work to pull from the database using MIN() and MAX() instead of pulling all of the data, sorting, and extracting min/max values.
Reporting and Analysis: Reports often highlight best or worst cases, and using MIN() and MAX() helps generate such insights automatically.

Master SQL: Empower Your Data Skills Today!

Unlock the power of databases with hands-on SQL training and become a data-driven professional

Explore Program

Syntax of MIN() and MAX() Functions in SQL

Syntax of the MIN() function:

SELECT MIN(clm_name)
FROM table_name 
[WHERE cond];

Syntax of the MAX() function:

SELECT MAX(clm_name) 
FROM table_name 
[WHERE cond];

Before moving forward with the MIN() and MAX() functions, we need to create a table for Device Maintenance.

CREATE TABLE dvc_mnt (
    tckt_id INT PRIMARY KEY,
    dvc_owner VARCHAR(60),
    city VARCHAR(50),
    dvc_type VARCHAR(40),
    issue VARCHAR(100),
    cost DECIMAL(10,2),
    tch_name VARCHAR(50)
);

INSERT INTO dvc_mnt (
    tckt_id, dvc_owner, city, dvc_type, issue, cost, tch_name
) VALUES
(101, 'Sanjay', 'Mysuru', 'Router', 'Frequent disconnections', 750.00, 'Manoj'),
(102, 'Ritika', 'Kochi', 'Smartphone', 'Battery overheating', 1200.00, 'Neeraj'),
(103, 'Aravind', 'Jodhpur', 'Printer', 'Paper jam issue', 950.00, 'Sowmya'),
(104, 'Ishita', 'Trichy', 'Laptop', 'Screen flickering', 3000.00, 'Karthik');

Select * from dvc_mnt

This is how the table looks once it is created and inserted with values.

Examples of MIN() and MAX() Functions in SQL

These MIN() and MAX() functions in SQL can be used along with GROUP BY and HAVING clauses, making them essential for data manipulation and analysis.

Using MIN() functions in SQL

This MIN() function returns the lowest or minimum value of the specified column.

Example:

-- To find the cheapest cost

SELECT MIN(cost) AS lowest_cost 
FROM dvc_mnt;

Output:

Explanation: Here, the lowest expected cost in the dataset is returned by the MIN(cost) function extracts the smallest value from the cost column.

Using MAX() Functions in SQL

The Largest (maximum) value from a given column is returned by the MAX() function.

Example:

-- To find the highest estimated cost

SELECT MAX(cost) AS highest_cost 
FROM dvc_mnt;

Output:

Explanation: Here, to determine the highest estimated cost in the dataset, the MAX(cost) function extracts the largest value from the cost column.

Get 100% Hike!

Master Most in Demand Skills Now!

Using GROUP BY with MIN() and MAX() Functions in SQL

In MySQL, you can use the GROUP BY clause and aggregation functions to aggregate a group of rows with the same value in a particular column. With the GROUP BY clause, we can determine totals and averages for each group of rows with aggregate functions.

Syntax:

SELECT column_name, MIN(column_name)/MAX(column_name)
FROM table_name
GROUP BY column_name;

Example:

-- To find the minimum cost for each city

SELECT city, MIN(cost) AS total_cost  
FROM dvc_mnt  
GROUP BY city;

Output:

Explanation: Here, the minimum costs for each region are determined by MIN(cost), and records are grouped by city using GROUP BY city.

Example:

-- To find the maximum cost for each city

SELECT city, MAX(cost) AS total_cost  
FROM dvc_mnt  
GROUP BY city;

Output:

Explanation: Here, the maximum costs for each region are determined by MAX(cost), and records are grouped by city using GROUP BY city.

Using HAVING with MIN() and MAX() Functions in SQL

In MySQL, after aggregating the columns, the HAVING clause is used to filter grouped results.

Syntax:

SELECT column_name, FUN(column_name)   
FROM tab_name  
GROUP BY column_name  
HAVING cond;

Example:

SELECT 
    city,
    MIN(cost) AS Min_Cost,
    MAX(cost) AS Max_Cost
FROM 
    dvc_mnt
GROUP BY 
    city
HAVING 
    MAX(cost) > 2000
    OR MIN(cost) < 700;

Output:

Using HAVING with MIN() and MAX() Functions in SQL

Explanation: Here, this query returns the maximum repair costs, which are greater than 2000, or the minimum repair costs, which are less than 700.

Real-world Example

University System: Consider a university that wants to track the performance of the students by each department.

Example:

CREATE TABLE stud (
    st_id INT PRIMARY KEY,
    st_name VARCHAR(100),
    dept VARCHAR(50),
    gpa DECIMAL(3,2)
);

INSERT INTO stud (st_id, st_name, dept, gpa) VALUES
(101, 'Aarav', 'Computer Science', 3.80),
(102, 'Bhavana', 'Mechanical', 3.40),
(103, 'Chirag', 'Electrical', 3.90),
(104, 'Deepika', 'Computer Science', 3.65),
(105, 'Eshan', 'Mechanical', 3.50),
(106, 'Farah', 'Civil', 3.20),
(107, 'Gautam', 'Electrical', 3.95),
(108, 'Harini', 'Computer Science', 3.70),
(109, 'Ishaan', 'Civil', 3.10),
(110, 'Jhanvi', 'Mechanical', 3.55);

SELECT 
    MAX(gpa) AS highest_gpa,
    MIN(gpa) AS lowest_gpa
FROM stud;

Output:

Explanation: Here, MAX(GPA) and MIN(GPA) give the highest and lowest GPA of the students, respectively.

Common Errors to Avoid

Using WHERE instead of HAVING: You can’t use WHERE to filter on a MIN() or MAX(). You have to use the HAVING clause after aggregation.
Not Using GROUP BY: When getting MIN or MAX per GROUP (for example, per city or department), without GROUP BY, you are going to get wrong or unexpected results.
Mistaken Column Selection: Errors might occur when you select non-aggregated columns without appropriately grouping them (this particularly matters in strict SQL mode).

Best Practices

Use GROUP BY when necessary: If you need minimum or maximum values per category (like per city or department), use MIN() or MAX() with a proper GROUP BY.
Pay Attention to NULLs: Make sure you remember that MIN() and MAX() will ignore NULLs by default; If NULLs are important in your logic, make sure you use something like COALESCE() or some other IS NULL checks.
Use HAVING for Aggregate Filtering: Whenever you are filtering based on MIN() or MAX() results, you want to make sure that you are using the HAVING clause, not the WHERE clause.

Become a Data Pro – Free SQL Course Inside!

Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course

Explore Program

Conclusion

MIN() and MAX() are important SQL tools used to find the minimum and maximum values of datasets. They are also very important tools used in reporting, analysis, and decision-making processes in business industries. They are, however, best operated under best practice rules such as correctly addressing NULLs, utilizing GROUP BY and HAVING if necessary, and performance optimizing through indexes. In this blog, you have gained knowledge on the MIN() and MAX() functions in detail.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, prepared by industry experts.