MIN and MAX in SQL
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.