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