How to work with MySQL Least and MySQL Greatest

In this article, we will learn how to work with MySQL Least and MySQL Greatest functions.

We will use MySQL version 8.0 to build the sample queries presented in this article.

MySQL Least function

This function is used to find the lowest value among a list of values that we must send it as an input parameter.

This function have different behaviors according to the data type of the arguments. If all values in the list are integers, they will be compared as integers. If the values in the list are strings, they will be compared as strings. But, if we send a combination of numeric values and strings, they will be compared as strings. Finally, if any of the values that we send as parameters is NULL, the function will return NULL. 

Syntax

SELECT LEAST(val1, val2, val3..., valn-1, valn);

Script 1. MySQL Least function syntax

Examples of use:

We can use the function to find the lowest value in a list of numbers (as we can see, zero is a valid value):

SELECT LEAST(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) as `The lowest value in the list`;

Script 2. MySQL Least example 1

Result obtained when executing the previous query:

The lowest value in the list

0

Table 1. Example 1 result

Now let’s see what happens when we pass NULL as any of the arguments:

SELECT LEAST(0,1,2,3,4,5,6,7,8,9,10,NULL,11,12,13,14,15) as `The lowest value in the list`;

Script 3. MySQL Least example 2

When executing the previous query, we will obtain the following result:

The lowest value in the list

NULL

Table 2. Example 2 result

The function also considers negative values:

SELECT LEAST(-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) as `The lowest value in the list`;

Script 4. MySQL Least example 3

Result obtained when executing the previous query:

The lowest value in the list

-1

Table 3. Example 3 result

Finally, we can also use the Least function to find the lowest value in a list of strings:

SELECT LEAST ('alpha','beta','gamma','delta','epsilon','zeta','eta','theta','iota','kappa') as 'The lowest value in the list'

Script5. MySQL Least example 4

When executing the previous query, we will obtain the following result:

The lowest value in the list

alpha

Table 4. Example 4 result

MySQL Greatest function

This function helps us to find the highest value in a given list. To perform the comparison between values, the same rules and considerations of the MySQL Least function are used.

Syntax

SELECT GREATEST(val1, val2, val3..., valn-1, valn);

Script 6. MySQL Greatest function syntax

Examples of use:

Example 1

SELECT GREATEST(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) as `The highest value in the list`;

Script 7. MySQL Greatest example 1

The highest value in the list

15

Table 5. Example 1 result

Example 2

SELECT GREATEST ('alpha','beta','gamma','delta','epsilon','zeta','eta','theta','iota','kappa') as 'The highest value in the list'

Script 8. MySQL Greatest example 2

The highest  value in the list

zeta

Table 6. Example 2 result

Example 3

SELECT GREATEST ('alpha',2,'gamma','delta',3,'zeta','eta','theta',5,'kappa') as 'The highest value in the list'

Script 9. MySQL Greatest example 3

The highest  value in the list

zeta

Table 7. Example 3 result