GREATEST() function in MySQL

The GREATEST() function in MySQL is designed to return the largest value from a list of expressions. It is particularly useful for identifying the maximum value among multiple columns or literal values, simplifying tasks that involve comparisons of multiple values. In this article, We will learn about the GREATEST() function in MySQL in detail and so on.

GREATEST() function in MySQL

• The GREATEST() function in MySQL returns the largest (maximum) value from a list of expressions.
  
• It compares two or more expressions and returns the highest value.
  
• This function is particularly useful when we need to determine the maximum value from multiple columns or a list of literal values.

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.

Examples of GREATEST() function in MySQL

Example 1

Finding Greatest number between given numbers using GREATEST() function.

SELECT GREATEST(10, 20, 30, 40, 50) AS Greatest_Value;

Output:

Explanation: This query uses the `GREATEST()` function to find the maximum value among the numbers 10, 20, 30, 40, and 50. The result, 50, is returned as `Greatest_Value`.

Example 2

Finding Greatest value between given string using GREATEST() function.

SELECT GREATEST('MySQL', 'MS ACCESS', 'SQL') AS  GreatestValue_String;

Output:

Explanation: This query uses the `GREATEST()` function to find the maximum value among the strings 'MySQL', 'MS ACCESS', and 'SQL', based on alphabetical order. The result, 'MySQL', is returned as `GreatestValue_String`.

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 :

Explanation: In this query, the `GREATEST()` function is used to determine the maximum value among the numbers 10, 20, 30, 40, 50, and `NULL`. Since `NULL` is present, the function will return `NULL` as the result, because any comparison involving `NULL` yields `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 ;

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:

Explanation:

This SQL query selects the Student_id and Student_name from the Student table, and calculates the highest mark among four subjects (Subject1, Subject2, Subject3, Subject4) for each student using the GREATEST() function.

Conclusion

The GREATEST() function is a versatile tool in MySQL for finding the highest value in a set of expressions. Whether dealing with numeric values or strings, it efficiently determines the largest item in the provided list. By using this function, users can quickly identify the maximum value, even in complex queries involving multiple columns and data types.

What happens if the GREATEST() function is used with only one value?

If GREATEST() is used with only one value, it returns that value as the result, since there is no other value to compare it against.

How does GREATEST() handle NULL values in its arguments?

If any of the arguments to GREATEST() is NULL, the function will return NULL because comparisons involving NULL produce NULL as the result.

Can GREATEST() be used with mixed data types (e.g., numbers and strings)?

GREATEST() can handle mixed data types, but it converts all arguments to the same data type for comparison. For numeric comparisons mixed with strings, strings are converted to numbers if possible, otherwise, they are compared based on their alphabetical order