MySQL greatest

Introduction to MySQL greatest

When we have to find out the largest value among multiple values in MySQL, we can make use of the MySQL greatest() function which is one of the available functions in MySQL that help us to manipulate the data and retrieve the required values. MySQL greatest() function is introduced in MySQL 4.0 and is available for all the versions with MySQL 4.0 and above. While using this function with multiple arguments that are passed in the list format as that of different parameters to the function, we need to consider and know beforehand the type of the values we are specifying and how the function works for different data type values.

In this article, we will learn about the syntax of the MySQL greatest() function and also see how the function works internally for different datatypes by looking at the rules and implement the function in some of the examples to clear our concept.

Syntax

The general syntax of using the MySQL greatest() function is as follows –

GREATEST(expression1, expression2, ...);

We can observe that the greatest() function can accept as many arguments that we wish to give. All the values or expressions should be specified in the comma-separated format and be enclosed in the functional brackets ().

Return Value – The value that is returned from the function is the greatest value among all the listed parameter values. The type of the returned value depends on the type of the supplied parameter values.

Rules followed while evaluating the greatest value

The function simply returns the error saying the parameter count is incorrect for the greatest() function to execute when only one parameter is passed. But when we pass multiple fields as the parameters to the function, the greatest() function follows certain rules to evaluate the maximum value out of all the specified ones that are listed as below –

• If the passed parameters contain even a single value or expression that evaluates or has value as NULL, then the greatest() function will return NULL value without doing any comparisons.
• When all the specified values are of the integer-valued datatype, the comparison is made considering all of them as integers.
• Whenever the specified list of arguments consists of at least a single double value, then the comparison is made by considering all the values as double-precision values. Other than that, if the list contains at least a single parameter with a DECIMAL value, then all values are considered as DECIMAL, and a comparison of DECIMAL values is done.
• If the list of arguments contains some of the values of integer type and some in String datatype in mixed fashion, then the comparison is made by considering all of them of string type.
• If the list of arguments contains at least one argument having a non-binary character or string, then the comparison is made considering all the specified arguments as non-binary string values.
• Other than the above-mentioned cases, the comparison is made considering all the arguments as binary strings.

Example

Let us first consider what happens when we use the function with none of the arguments –

SELECT GREATEST();

that gives the following output with an error saying that the number of parameters passed is incorrect for the function to execute, as shown below

Passing a single value with null, any integer value, string value, or boolean, decimal value returns the same output with an error. Consider the execution of the below query statements –

SELECT GREATEST(null);

SELECT GREATEST(1);

SELECT GREATEST("1");

SELECT GREATEST(1.00);

The output of the execution of the above statements is as follows, throwing the same error for blank argument list saying a count of parameters is incorrect –

Let us now consider a list having all integer values except one which has a null value in it and observes the output. Consider the following query statement –

SELECT GREATEST(441, 532, 933, NULL, 968, 87, 564);

The output of the execution of the above statements is as follows, getting NULL value as maximum value –

We can overcome this problem of ignorance of comparison values when the list may contain NULL value by using IFNULL() or COALESCE() functions to handle NULL values.

Now, we will consider all the integer values and find the greatest values out of all of them using the GREATEST() function. Consider the following query statement –

SELECT GREATEST(441, 532, 933, 856, 968, 87, 564) AS greatest_value;

Let us consider a list of double and integer values combined as shown in the below query statement.

SELECT GREATEST(441.02, 532, 933.58, 856, 968, 87.63, 564) AS greatest_double_value;

The output of the execution of the above statements is as follows –

We can observe from the output as even though the greatest value among the specified parameters is 968 is an integer, as the list contains at least one parameter that is a double value, the integer value is also considered as double, and hence the retrieved maximum value is in the format of a double value.

Consider the following query statement consisting of the characters as the parameters in it, and we are trying to find the maximum value out of them using the GREATEST() function as shown below

SELECT GREATEST('A','Z','P','J','G') AS greatest_string_value;

The output of the execution of the above statements is as follows –

Note that the ASCII values are considered while comparing characters and strings in MySQL GREATEST() function.

Now, we will create one table named comparison_values using the following statement –

CREATE TABLE `comparison_values` (
`number1` int(11),
`number2` int(11),
`number3` int(11),
`number4` int(11),
`number5` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Let us insert some values –

INSERT INTO `comparison_values` VALUES
(456, 532, 345, 856, 44),
(441, NULL, 456, 65, 968),
(324, 532, 324, 856, 64),
(243, 54, 933, NULL, 65);
select * from `comparison_values`;

that gives the following output after the execution of the query –

Let us select the maximum value out of the records of comparison_values table using the following query –

SELECT GREATEST(number1,number2,number3,number4,number5) AS greatest_value FROM comparison_values;

that gives the following output after the execution of the query –

To avoid NULL comparisons, let us use IFNULL as follows –

SELECT GREATEST(IFNULL(number1,0),IFNULL(number2,0),IFNULL(number3,0),IFNULL(number4,0),IFNULL(number5,0)) AS greatest_value FROM comparison_values;

that gives the following output after the execution of the query –

Conclusion – MySQL greatest

We can use the GREATEST() function to find out the maximum value that is the largest value out of the specified values in MySQL.

Recommended Articles

This is a guide to MySQL greatest. Here we discuss the Rules followed while evaluating the greatest value along with the Examples. You may also have a look at the following articles to learn more –

1. MySQL DELETE Trigger
2. MySQL CEIL
3. MySQL Flush Log
4. MySQL WEEKDAY