GREATEST() function in MySQL
GREATEST() function in MySQL is used to find greatest values from given arguments respectively. If any given value is NULL, it returns NULLs. Otherwise, it returns the greatest value.
Syntax :
GREATEST(X1, X2, X3, ...)
Parameter : This method accepts N parameters in the syntax, as mentioned above and described below :
X1, X2, X3… : The list of values from which greatest to be evaluated.
Returns : It returns the greatest value.
Example-1 :
Finding Greatest number between given numbers using GREATEST() function.
SELECT GREATEST(10, 20, 30, 40, 50) AS Greatest_Value;
Output :
Greatest_Value |
---|
50 |
Example-2 :
Finding Greatest value between given string using GREATEST() function.
SELECT GREATEST('MySQL', 'MS ACCESS', 'SQL') AS GreatestValue_String;
Output :
GreatestValue_String |
---|
SQL |
Example-3 :
Finding Greatest number between given numbers using GREATEST() function when there is a NULL Value.
SELECT GREATEST(10, 20, 30, 40, 50, NULL) AS Greatest_Value;
Output :
Greatest_Value |
---|
NULL |
Example-4 :
The GREATEST function can also be used to find the Greatest value between column data. To demonstrate create a table named Student.
CREATE TABLE Student( Student_id INT AUTO_INCREMENT, Student_name VARCHAR(100) NOT NULL, Student_Class VARCHAR(20) NOT NULL, Subject1 INT NOT NULL, Subject2 INT NOT NULL, Subject3 INT NOT NULL, Subject4 INT NOT NULL, PRIMARY KEY(Student_id ) );
Now insert some data to the Student table –
INSERT INTO Student(Student_name, Student_Class, Subject1, Subject2, Subject3, Subject4) VALUES ('Sayan', 'X', 81, 90, 86, 92 ), ('Nitin', 'X', 90, 84, 88, 91 ), ('Aniket', 'X', 81, 80, 87, 95 ), ('Abdur', 'X', 85, 90, 80, 90 ), ('Sanjoy', 'X', 88, 82, 84, 90 );
Showing all data in Student Table –
Select * From Student ;
Student_id | Student_name | Student_Class | Subject1 | Subject2 | Subject3 | Subject4 |
---|---|---|---|---|---|---|
1 | Sayan | X | 81 | 90 | 86 | 92 |
2 | Nitin | X | 90 | 84 | 88 | 91 |
3 | Aniket | X | 81 | 80 | 87 | 95 |
4 | Abdur | X | 85 | 90 | 80 | 90 |
5 | Sanjoy | X | 88 | 82 | 84 | 90 |
Now, we are going to find the greatest marks for every student in all subjects.
Select Student_id, Student_name, GREATEST(Subject1, Subject2, Subject3, Subject4) AS Greatest_Mark FROM Student;
Output :
Student_id | Student_name | Greatest_Mark |
---|---|---|
1 | Sayan | 92 |
2 | Nitin | 91 |
3 | Aniket | 95 |
4 | Abdur | 90 |
5 | Sanjoy | 90 |
Contact Us