How the LEAST() function works in Mariadb?

The LEAST() function is a numeric function that returns the smallest value among a list of values. It can be used to compare two or more numeric expressions, such as column values, constants, or variables. The LEAST() function can also be applied to date and time values, in which case it returns the earliest value.

Syntax

The syntax of the LEAST() function is as follows:

LEAST(value1, value2, ..., valueN)

The value1, value2, ..., valueN are the values that you want to compare. They can be any valid numeric or date and time expressions in Mariadb.

The LEAST() function returns the smallest value among the input values. If any of the input values is NULL, the function returns NULL.

Examples

In this section, we will show some examples of how to use the LEAST() function in Mariadb.

Example 1: Comparing numeric values

The following example shows how to use the LEAST() function to compare three numeric values and return the smallest one.

SELECT LEAST(10, 5, 7);

The output is:

5

As you can see, the LEAST() function returns the smallest value among the input values, which is 5.

Example 2: Comparing column values

The following example shows how to use the LEAST() function to compare the values of two columns and return the smaller one. Suppose we have a table called products that stores the product information, such as name, price, and quantity. The table has the following data:

We can use the LEAST() function to compare the price and quantity columns and return the smaller one, as shown below:

SELECT id, name, LEAST(price, quantity) AS smaller FROM products;

The output is:

As you can see, the LEAST() function returns the smaller value between the price and quantity columns for each row.

Example 3: Comparing date and time values

The following example shows how to use the LEAST() function to compare three date and time values and return the earliest one.

SELECT LEAST('2024-02-12 22:03:36', '2024-01-01 00:00:00', '2024-02-29 23:59:59');

The output is:

2024-01-01 00:00:00

As you can see, the LEAST() function returns the earliest value among the input values, which is 2024-01-01 00:00:00.

Example 4: Comparing values with different data types

The following example shows how to use the LEAST() function to compare values with different data types, such as numeric, string, and date and time. In this case, the LEAST() function will try to convert the values to a common data type before comparing them. If the conversion fails, the function will return NULL.

SELECT LEAST(10, '5', '2024-02-12');

The output is:

5

As you can see, the LEAST() function converts the string '5' to a numeric value and compares it with the numeric value 10. The string '2024-02-12' is not a valid numeric value, so the function ignores it. The smallest value among the input values is 5.

Related Functions

There are some other functions that are related to the LEAST() function in Mariadb. They are:

• The GREATEST() function: This function returns the largest value among a list of values. It is the opposite of the LEAST() function. For example, GREATEST(10, 5, 7) returns 10.
• The MIN() function: This function returns the minimum value of a set of values. It is similar to the LEAST() function, but it can only be used with an aggregate expression, such as a column name or a subquery. For example, SELECT MIN(price) FROM products returns the lowest price among all the products.
• The MAX() function: This function returns the maximum value of a set of values. It is similar to the GREATEST() function, but it can only be used with an aggregate expression, such as a column name or a subquery. For example, SELECT MAX(quantity) FROM products returns the highest quantity among all the products.

Conclusion

In this article, we have learned how the LEAST() function works in Mariadb. We have seen the syntax of the function, and some examples of how to use it with different types of values. We have also learned about some related functions that can be used with the LEAST() function. The LEAST() function is a useful function that can help us compare values in Mariadb.