w3resource

MySQL GREATEST() function

GREATEST() function

MySQL GREATEST() function returns the greatest of the given arguments.

This function is useful in -

  • It returns the largest value from a list of expressions.
  • It helps to prevent errors that may occur due to incorrect handling of maximum values.
  • By using GREATEST(), you can ensure that you're always getting the highest value.
  • It simplifies conditional logic by providing a concise way to find the maximum value without the need for complex CASE statements or multiple comparisons.
  • In scenarios where the data is dynamic and the maximum value may change, GREATEST() provides an easy way to retrieve the current maximum value.
  • GREATEST() can handle different data types, allowing you to compare and find the maximum value regardless of the data type of the expressions.

Syntax:

GREATEST(value1,value2,...)

MySQL Version: 8.0

Example: MySQL GREATEST() function

The following MySQL statement will retrieve the greatest argument for the list of arguments.

Code:

SELECT GREATEST(15,10,25);

Output:

mysql> SELECT GREATEST(15,10,25);
+--------------------+
| GREATEST(15,10,25) |
+--------------------+
|                 25 | 
+--------------------+
1 row in set (0.01 sec)

Example : GREATEST() function with WHERE clause

The following MySQL statement will fetch those books (along with their date of publish and number of pages) from <i>book_mast</i> table which has more pages than the return value of GREATEST(200,300,395), i.e. 395.

Code:

SELECT book_name,dt_of_pub,no_page
FROM book_mast
WHERE no_page>GREATEST(200,300,395);

Sample table: book_mast


Output:

mysql> SELECT book_name,dt_of_pub,no_page
    -> FROM book_mast
    -> WHERE no_page>GREATEST(200,300,395);
+--------------------------------+------------+---------+
| book_name                      | dt_of_pub  | no_page |
+--------------------------------+------------+---------+
| Guide to Networking            | 2002-09-10 |     510 | 
| Transfer  of Heat and Mass     | 2004-02-16 |     600 | 
| Fundamentals of Thermodynamics | 2002-10-14 |     400 | 
+--------------------------------+------------+---------+
3 rows in set (0.00 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: Greater than operator(>)
Next: IN()



Follow us on Facebook and Twitter for latest update.