How To Use MAX() Function In MySQL

Introduction

In MySQL, the MAX() Aggregate function is used to return the highest value in a set of values in the column. This Aggregate function can be used in various scenarios, such as finding the maximum price of a product, the highest score in a test, or the most recent date in a table with the help of the MAX() Aggregate function.

Here is a step-by-step Explanation of how to use the MAX() Aggregate function in MySQL with examples.

Syntax 

SELECT MAX(DISTINCT aggregate_expression)    
FROM table_name(s)    
[WHERE conditions]; 

• aggregate_expression: It Contains the column, expression, or formula from which the maximum value will be returned.
• table_name(s): this parameter specifies the table's name from where we want to retrieve records.
• WHERE conditions: It is optional. In this field, the conditions must be fulfilled for the records to be selected.

Step 1. Create a sample table. First, we need to create a sample table to demonstrate the use of the MAX() Aggregate function.

CREATE TABLE students (
	id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255),
       score int,
     city VARCHAR(255));

Step 2. Insert sample data. Let's insert some sample data into the student's table to demonstrate the use of the MAX() Aggregate function.

INSERT INTO students (name, score, city) VALUES
('Sachin', 85.50, 'sitapur'),
('Aman', 81.50, 'sitapur'),
('sudhir', 86.50, 'sitapur'),
('Priyanshu', 90.25,'Kanpur'),
('Abhishek', 87.25,'Kanpur'),
('Vijay', 80.25,'Kanpur'),
('Mohit', 95.75,'Noida'),
('Rohit', 76.75,'Noida'),
('Akash', 84.75,'Noida'),
('Harshit', 88.00,'Lucknow'),
('Punar', 90.00,'Lucknow'),
('Shubhankar', 80.00,'Lucknow');

Output

Step 3. Use the MAX() Aggregate function To find the highest score in the student's table. We can use the MAX() Aggregate function. Here's an example query-

select MAX(score) as Max_Score from students;

Output

This query will return the highest score from the score column of the student's table, which is 95.75 in our sample data.

Step 4. Using the MAX() Aggregate function with GROUP BY We can also use the MAX() Aggregate function with the GROUP BY clause to find the highest score for each student.

SELECT city as City_Name, MAX(score) as Max_score FROM students GROUP BY city;

Output

Conclusion

The MAX() Aggregation function is a very useful function in MySQL that allows us to find the highest value in a set of values. It can be used in various scenarios, such as finding the highest score of a student or the most recent date in a table. By following the steps outlined in this article, you can easily use the MAX() Aggregate function in your MySQL queries.