MySQL GREATEST() function

Last update on May 22 2024 13:21:37 (UTC/GMT +8 hours)

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