EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL greatest
 

MySQL greatest

Updated June 8, 2023

MySQL greatest

 

 

Introduction to MySQL greatest

When we have to find out the largest value among multiple values in MySQL, we can use the MySQL greatest() function, one of the available functions that help us manipulate the data and retrieve the required values. MySQL’s most significant () function is introduced in MySQL 4.0 and is available for all the versions of MySQL 4.0 and above. While using this function with multiple arguments passed in the list format as 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Syntax

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

GREATEST(expression1, expression2, ...);

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

Return Value – The value 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 greatest() function throws an error if it receives only one parameter, stating that the parameter count is incorrect for execution. However, when the function is passed multiple fields as parameters, it follows specific rules to evaluate and determine the maximum value among all the specified ones listed below.

  • If the passed parameters contain even a single value or expression that evaluates or has a value as NULL, then the greatest() function will return a NULL value without doing any comparisons.
  • When the integer-valued datatype encompasses all the specified values, the comparison uses them as integers.
  • If the specified list of arguments contains at least one double value, the function treats all values as double-precision values for comparison. Conversely, if the list includes at least one parameter with a DECIMAL value, the function considers all values as DECIMAL and compares them accordingly.
  • When the list of arguments contains a mix of values, including some of the integer type and some of the string datatype, the comparison considers all of them as string type.
  • Suppose the list contains at least one argument with a non-binary character or string. In that case, the comparison is made considering all the specified arguments as non-binary string values.
  • Besides the cases mentioned above, the comparison considers 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();

The function outputs an error indicating that the function cannot execute due to an incorrect number of parameters passed, as shown below.

MySQL greatest output 1

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

SELECT GREATEST(null);

MySQL greatest output 2

SELECT GREATEST(1);

MySQL greatest output 3

SELECT GREATEST("1");

MySQL greatest output 4

SELECT GREATEST(1.00);

MySQL greatest output 5

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

Let us consider a list with all integer values except one with a null value and observe 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 –

output 6

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;

output 7

As shown in the query statement, let us consider a list of double and integer values combined.

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 –

output 8

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 –

output 9

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 –

output 10

Let us select the maximum value out of the records of the 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 –

output 11

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 –

output 12

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

We hope that this EDUCBA information on “MySQL greatest” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW