MySQL GREATEST function

This statement displays the greatest values of the table. The GREATEST function returns a null value when the table contains a null value. The GREATEST function needs a minimum of two columns of the table. If the character value is available in the column, then the function returns the first descending value of the alphabet.

Syntax

The GREATEST function syntax shows below.

GREATEST (data1, data2, data3, dataN);

MySQL GREATEST function with table syntax shows below.

SELECT GREATEST (Column1, Column2, Column3, ColumnN) 
FROM table name WHERE condition;

Prerequisite for GREATEST comparison function

• Create a new table in the database. We can use the below statement to do this:

CREATE TABLE `world`.`crafts` (

  `nos` INT NOT NULL,

  `items` VARCHAR(45) NULL,

  `quantity` INT NULL,

  `available` INT NULL,

  PRIMARY KEY (`nos`));

• Insert value in the given table as per requirement.
• MySQL output command shows table format and its information.

 mysql> select * from crafts;

Important points of the GREATEST function

• The GREATEST function compares the multiple column data.
• This function is used to find the largest values of the numerical table data.
• This function returns the last character of the alphabet as the largest value.
• If a null value is available in the column, then this function returns null data.

Example of the GREATEST function

Example1: Execute the below query to know about the "GREATEST" statement

mysql> SELECT GREATEST (25, 28, 60, 45, 39);

Output

This query returns the below result where the largest value is 60.

Example2: It is another example where the function finds the largest value multiple times.

mysql> SELECT GREATEST (0, 1, 2, 0, 1, 2);

Output

This query returns the below result where the largest value is 2 because it removes duplicates and displays only the single largest value.

Example3: the GREATEST function example with NULL value.

mysql> SELECT GREATEST (25, 28, 60, 45, 39, NULL);

Output

It returns the below output as the function returns null if it contains a NULL value.

Example4: The below example will explain this function with the character values.

mysql> SELECT GREATEST ("good", "bad", "wow", "perfect", "simple", "better");

Output

It returns the result “wow” because it is the largest alphabetical value in specified values.

Example5: Execute the below query to know how the "GREATEST" statement works with the WHERE clause. Here WHERE clause applies on the "nos" column.

mysql> select greatest(nos, quantity, available) AS greatest from crafts where nos < 3;

Output

Executing the statement will show the below output that shows two rows and one column. The image displays the greatest value from the three columns:

Example7: Execute the below query to know about the "GREATEST" statement with the ORDER BY clause.

mysql> select greatest(nos, quantity, available) AS greatest from crafts order by greatest ASC;

Output

It gives the below output:

The image displays the greatest value from the three columns. Here, the numerical value displays in the output table. The output table shows two rows and one column. Here, table data show in ascending order.