The GREATEST() function extracts the greatest or largest value from a set of values. It needs at least one argument to work with, and if you mix different types, like a text and a number, it will return an error.
For example, comparing the greatest value among 4, "two", and 9 would result in an error.
The syntax for the GREATEST() function is as follows:
- value_1: Represents the first value.
- value_n: Represents one or more additional values, separated by commas.
- NULL values within the expressions are ignored.
- The result will be NULL if all expressions evaluate to NULL.
Here are examples that illustrate the usage of the GREATEST() function:
Consider the following example:
The query will return 3, the smallest value among the provided values.
String comparison is also supported, as shown below:
In this case, the result will be 'cherry', the greatest string according to the order.
NULL values are ignored when determining the greatest value:
The result will be the greatest non-NULL value, which is 9.
Negative numbers can also be compared:
This query will return 8, the greatest value among the provided numbers.
The GREATEST function can also be used to find the Greatest value between column data. For example, let’s create a table named Student that stores students' names and scores.
Use the SELECT statement to view all the records:
Now, we will find the greatest marks for every student in all subjects.