SQL MIN and MAX Functions : Syntax & Examples
The SQL MIN and MAX function are aggregate functions that operate on group of data based on specific criteria and return a single value. The MIN function returns minimum value of the selected columns and the MAX function returns maximum value of selected columns. This article provides a detailed overview of MIN and MAX aggregate functions with example.
The main function of the MIN and MAX aggregate functions is to retrieve the minimum and maximum values from selected columns.
Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;
Demo Database
CREATE TABLE `users` ( `Id` int PRIMARY KEY AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Mobile` varchar(100) DEFAULT NULL, `DOB` date DEFAULT NULL, `Weight` decimal(10,2) DEFAULT NULL );
INSERT INTO Users (Name,Mobile,DOB,Weight)
VALUES ('Deepak', '9999999991', '1993-01-15', 60),
('Amar', '9999999999', '1994-11-20', 85),
('Naveen', '9999999992', '1997-01-15', 55),
('Ramesh', '9999999993', '1998-08-03', null),
('Harsh', '9999999995', '1993-10-05', 73),
('Deepak', '9999999391', '1983-01-15', 90),
('Amar', '9999999969', '1984-11-20', 85),
('Naveen', '9999999492', '1987-01-15', 59),
('Ramesh', '9999999693', '1988-08-03', 86),
('Harsh', '9999998975', '1983-10-05', 70);MIN function
MIN aggregate function is used to get minimum value of the selected columns
SQL query to get minimum Weight of Users.
SELECT MIN(Weight) FROM Users;
MIN(Weight) 55.00
SQL query to get minimum Weight of Users where user name contains `r`.
SELECT MIN(Weight) FROM Users WHERE Name like "%r%";
MIN(Weight) 70.00
MIN function With GROUP BY
The syntax for using MIN with GROUP BY in SQL is structured to perform aggregate calculations within each group defined by one or more columns
SELECT column1, column2, ..., MIN(aggregated_column) AS min_value FROM table_name GROUP BY column1, column2, ...;
SQL query to get minimum Weight of Users Group by user name.
SELECT Name, MIN(Weight) as Weight FROM Users GROUP BY Name;
Name Weight Deepak 60.00 Amar 85.00 Naveen 55.00 Ramesh 86.00 Harsh 70.00
SQL query to get minimum Weight of Users where user name contains `r` group by user name.
SELECT Name , MIN(Weight) Weight FROM Users WHERE Name like "%r%" GROUP BY Name;
Name Weight Amar 85.00 Ramesh 86.00 Harsh 70.00
MAX function
MAX aggregate function is used to get maximum value of the selected columns
SQL query to get maximum Weight of Users.
SELECT MAX(Weight) as Weight FROM Users;
Weight 90.00
SQL query to get maximum Weight of Users where user name contains `r`.
SELECT MAX(Weight) Weight FROM Users WHERE Name like "%r%";
Weight 86.00
MAX with GROUP BY
The syntax for using MAX with GROUP BY in SQL is structured to perform aggregate calculations within each group defined by one or more columns.
SELECT column1, column2, ..., MAX(aggregated_column) AS max_value FROM table_name GROUP BY column1, column2, ...;
SQL query to get maximum Weight of Users Group by user name.
SELECT Name, MAX(Weight) as Weight FROM Users GROUP BY Name;
Name Weight Deepak 90.00 Amar 85.00 Naveen 59.00 Ramesh 86.00 Harsh 73.00
SQL query to get maximum Weight of Users where user name contains `r` group by user name.
SELECT Name, MAX(Weight) Weight FROM Users WHERE Name like "%r%" GROUP BY Name;
Name Weight Amar 85.00 Ramesh 86.00 Harsh 73.00
Key Characteristics of MIN and MAX functions
- The SQL MIN and MAX function are aggregate functions that operate on group of data based on specific criteria and return a single value.
- The MIN function returns minimum value of the selected columns and the MAX function returns maximum value of selected columns
- The MIN() and MAX() functions are versatile, capable of operating on various data types such as numbers, strings, and dates
- The MIN() and MAX() functions are crucial functions of SQL for robust data management and analysis.
- We should use index columns with MIN and MAX to enhance query performance, especially when dealing with large datasets.
- By default, both functions ignore NULL values. If all values in the specified column are NULL the functions return NULL.
Frequently Asked Questions (FAQs)
1. What are MIN and MAX functions in SQL?
MIN and MAX are aggregate functions in SQL used to find the smallest and largest values from a column. They work on a set of rows and return a single value as the result. These functions are commonly used in reporting, analytics and data validation scenarios, such as finding the lowest salary, highest score, earliest date or latest timestamp in a table.
2. Do MIN and MAX ignore NULL values?
Yes, both MIN and MAX functions automatically ignore NULL values. If a column contains NULLs along with valid values, the functions consider only the non-NULL values. However, if all values in the column are NULL, the result of MIN or MAX will also be NULL. This behavior is consistent across most SQL databases.
3. Can MIN and MAX be used with text or date columns?
Yes. MIN and MAX work with numeric, date and text columns. For dates, MIN returns the earliest date and MAX returns the latest date. For text columns, results are determined based on alphabetical ordering, making them useful for finding first or last values.
4. What is the difference between MIN/MAX and ORDER BY with LIMIT?
MIN and MAX directly return a single value and are optimized for aggregation. ORDER BY with LIMIT retrieves full rows and may be slower on large datasets. MIN and MAX are preferred when only the extreme value is required.
5. Can MIN and MAX be used with GROUP BY?
Yes, MIN and MAX are frequently used with GROUP BY to calculate minimum or maximum values for each group, such as finding the minimum salary per department or the maximum score per user.
6. How do MIN and MAX behave when used with WHERE?
The WHERE clause filters rows before the MIN or MAX calculation is applied. Only rows that satisfy the WHERE condition are considered, allowing precise calculations based on specific criteria.
7. Do MIN and MAX return multiple rows?
No. MIN and MAX return a single value unless GROUP BY is used. With GROUP BY, one aggregated result is returned per group.
8. Can MIN and MAX improve query performance?
Yes. When used on indexed columns, databases can often retrieve minimum or maximum values efficiently without scanning the entire table, making these functions very fast on large datasets.
9. Can I use MIN and MAX in UPDATE or DELETE statements?
Yes. MIN and MAX can be used in subqueries within UPDATE or DELETE statements, such as updating records with the minimum value or deleting rows with the maximum value.
10. What happens if I use MIN or MAX without GROUP BY but select other columns?
This is not allowed in standard SQL. Non-aggregated columns must be included in GROUP BY. Otherwise, the database will throw an error because it cannot determine which row’s value to return.
11. Are MIN and MAX commonly used in real-world applications?
Yes. MIN and MAX are widely used in dashboards, reports, analytics and monitoring systems to find lowest prices, highest transactions, earliest dates or most recent activities.
12. Are MIN and MAX frequently asked in SQL interviews?
Yes. Interviewers often ask about MIN and MAX with GROUP BY, WHERE, HAVING, indexes, NULL handling and performance considerations. Understanding these functions shows strong SQL fundamentals.