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.

Sample table: book_mast

Code:

-- This query selects specific columns from the 'book_mast' table where the number of pages is greater than the greatest value among 200, 300, and 395.
SELECT book_name, dt_of_pub, no_page
-- This statement specifies the columns to be retrieved: 'book_name', 'dt_of_pub', and 'no_page'.
FROM book_mast
-- This part of the query specifies the table from which data is being retrieved, which is 'book_mast'.
WHERE no_page > GREATEST(200, 300, 395);
-- This clause filters the rows to include only those where the 'no_page' column has a value greater than the greatest value among 200, 300, and 395.

Explanation:

• The purpose of this SQL query is to retrieve information about books that have more pages than the greatest value among 200, 300, and 395.


• SELECT book_name, dt_of_pub, no_page: This part of the query specifies the columns to be selected from the 'book_mast' table. It includes the book's name (book_name), date of publication (dt_of_pub), and number of pages (no_page).


• FROM book_mast: This part specifies the table from which the data is being selected, which is the 'book_mast' table.


• WHERE no_page > GREATEST(200, 300, 395): This clause filters the results to include only those rows where the no_page column has a value greater than the greatest value among 200, 300, and 395. The GREATEST() function returns the largest value among the specified parameters (in this case, 200, 300, and 395).

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()