SQL SELECT MAX
The MAX is an aggregate function in Structured Query Language that returns the largest value from the multiple values of the column in the table.
Syntax of SQL Max() Function
We can also use the WHERE condition for selecting the maximum value from the specific records.
Two Examples of SQL Max Function
In this article, you will learn how to use the MAX function in SQL by the following two examples.
Example 1: This example describes how to use the MAX function in SQL.
Let's create a simple table on which the MAX function is to be run.
The following CREATE TABLE statement creates the Cars_Details table with five fields:
(
Car_Number INT PRIMARY KEY,
Car_Model INT,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;
The following INSERT query inserts the records of cars into the Cars_Details table:
VALUES (2578, 2018, Creta, 3, 1500000),
(9258, 2019, Audi, 2, 3000000),
(8233, 2018, Venue, 6, 900000),
(8990, 2018, Nexon, 7, 700000),
(9578, 2020, Mercedes, 6, 8000000),
(1258, 2021, Thar, 2, 1500000),
(2564, 2019, Jaguar, 4, 6000000),
(2155, 2020, Scorpio, 8, 1800000);
The following SELECT query shows the data of the Cars_Details table:
| Car_Number | Car_Name | Car_Amount | Car_Price |
|---|---|---|---|
| 2578 | Creta | 3 | 900000 |
| 9258 | Audi | 2 | 1100000 |
| 8233 | Venue | 6 | 900000 |
| 8990 | Nexon | 7 | 700000 |
| 9578 | Mercedes | 6 | 8000000 |
| 1258 | Thar | 2 | 1500000 |
| 2564 | Jaguar | 4 | 6000000 |
| 2155 | Scorpio | 8 | 1800000 |
The following query shows the largest Car_Number from the above Car_Details table:
Output:
Example 2: This example describes how to use the MAX function with the WHERE clause in the SELECT statement:
The following query creates the College_Student_Details table using the CREATE TABLE statement:
(
Student_ID INT NOT NULL,
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT,
Student_Marks INT
);
The following SQL queries insert the record of students into the above table using INSERT INTO statement:
INSERT INTO College_Student_Details VALUES (102, Raman, MCA, 24, 98);
INSERT INTO College_Student_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO College_Student_Details VALUES (107, Vikash, B.tech, 20, 78);
INSERT INTO College_Student_Details VALUES (111, Monu, MBA, 21, 65);
INSERT INTO College_Student_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO College_Student_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO College_Student_Details VALUES (123, Divya, B.tech, 21, 89);
INSERT INTO College_Student_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO College_Student_Details VALUES (130, Nidhi, BBA, 20, 88);
INSERT INTO College_Student_Details VALUES (132, Priya, MBA, 22, 99);
INSERT INTO College_Student_Details VALUES (138, Mohit, MCA, 21, 92);
Let's see the record of the above table using the following SELECT statement:
| Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
|---|---|---|---|---|
| 101 | Anuj | B.tech | 20 | 88 |
| 102 | Raman | MCA | 24 | 98 |
| 104 | Shyam | BBA | 19 | 92 |
| 107 | Vikash | B.tech | 20 | 78 |
| 111 | Monu | MBA | 21 | 65 |
| 114 | Jones | B.tech | 18 | 93 |
| 121 | Parul | BCA | 20 | 97 |
| 123 | Divya | B.tech | 21 | 89 |
| 128 | Hemant | MBA | 23 | 90 |
| 130 | Nidhi | BBA | 20 | 88 |
| 132 | Priya | MBA | 22 | 99 |
| 138 | Mohit | MCA | 21 | 92 |
The following MAX query displays the highest marks under 80 from the above Student_Details table:
Output:
MAX Function with SQL GROUP BY clause
The MAX Function with GROUP BY clause shows the highest value in each group from the table.
The syntax to use Group BY Clause with MAX Function is given below:
Example of MAX Function with GROUP BY Clause
Let's take the above College_Student_Details table to understand the concept of MAX function With GROUP BY Clause.
The following query shows the highest marks of student in each course from the above College_Student_Details table:
Output:
| Student_Course | AVG (Student_Marks) |
|---|---|
| B.tech | 93 |
| MCA | 98 |
| BBA | 92 |
| MBA | 99 |
| BCA | 97 |
