MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL GREATEST function
2022-04-08 08:13:05

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;
MySQL GREATEST function

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.

MySQL GREATEST function

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.

MySQL GREATEST function

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.

MySQL GREATEST function

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.

MySQL GREATEST function

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:

MySQL GREATEST function

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:

MySQL GREATEST function

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.