MySQL LEAST() function

LEAST() function

MySQL LEAST() function returns the smallest argument from two or more arguments.

This function is useful in -

• LEAST() is useful in conditional logic, allowing you to determine the smallest value among a set of options, which is particularly useful in decision-making processes.
• It provides a convenient way to find the smallest value among a set of expressions or column values.
• LEAST() returns NULL if any of the input values are NULL.
• Using LEAST() makes queries more readable and understandable, as it clearly states the intention to find the minimum value.
• It can be used in mathematical calculations where you need to find the minimum value among a set of numeric expressions.
• This is especially useful when dealing with complex queries involving multiple conditions.

Syntax:

LEAST(value1,value2,...)

The arguments are compared using the following rules:

• If any argument is NULL, the result is NULL. No comparison is needed.
• If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
• If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.
• If the arguments comprise a mix of numbers and strings, they are compared as numbers.
• If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
• In all other cases, the arguments are compared as binary strings.

MySQL Version: 8.0

Example: MySQL LEAST() function

The following MySQL statement will find the smallest out of the list of arguments.

Code:

-- This query uses the LEAST() function to find the smallest value among the given arguments.
SELECT LEAST(15, 10, 25);
-- The LEAST() function compares the provided values and returns the smallest one.

Explanation:

• The purpose of this SQL query is to determine the smallest value among a set of provided values using the LEAST function.


• SELECT LEAST(15, 10, 25): This part of the query uses the LEAST function to compare the values 15, 10, and 25.


• The LEAST function evaluates the given arguments and returns the smallest value among them.


• In this case, it compares the three values: 15, 10, and 25.


• Among these values, 10 is the smallest.

Output:

Example: MySQL LEAST() function using string

The following MySQL statement will find the smallest out of the list of arguments. It returns M, since S and Z come after M.

Code:

-- This query uses the LEAST() function to find the smallest value among the given string arguments.
SELECT LEAST("Z", "M", "S");
-- The LEAST() function compares the provided string values lexicographically and returns the smallest one.

Explanation:

• The purpose of this SQL query is to determine the smallest string value among a set of provided string values using the LEAST function.


• SELECT LEAST("Z", "M", "S"): This part of the query uses the LEAST function to compare the string values "Z", "M", and "S".


• The LEAST function evaluates the given string arguments and returns the smallest value based on lexicographical (dictionary) order.


• In this case, it compares the three string values: "Z", "M", and "S".


• In lexicographical order, "M" comes before "S" and "Z".

Output:

Example: MySQL LEAST() function with where clause

The following MySQL statement will fetch those books from book_mast table which have less number of pages than lowest argument returning from the LEAST() function.

Sample table: book_mast

Code:

-- Selects the columns book_name, dt_of_pub, and no_page from the book_mast table
SELECT book_name, dt_of_pub, no_page
-- Specifies the table from which to retrieve the data
FROM book_mast
-- Filters the rows to include only those where the number of pages (no_page) is less than the smallest value among 500, 300, and 395
WHERE no_page < LEAST(500, 300, 395);

Explanation:

• SELECT book_name, dt_of_pub, no_page:


• This part of the query specifies the columns to be retrieved from the book_mast table.


• book_name: The name of the book.


• dt_of_pub: The date of publication of the book.


• no_page: The number of pages in the book.


• FROM book_mast:


• This specifies the table from which to retrieve the data. In this case, it is the book_mast table.


• WHERE no_page < LEAST(500, 300, 395):


• This clause filters the rows to include only those where the number of pages (no_page) is less than the smallest value among the numbers 500, 300, and 395.


• LEAST(500, 300, 395): This function compares the values 500, 300, and 395, and returns the smallest value, which is 300 in this case.


• So, the WHERE clause effectively becomes WHERE no_page < 300, filtering the rows to include only those books with fewer than 300 pages.

Output:

Slideshow of MySQL Comparison Function and Operators

Previous: ISNULL()
Next: LESS THAN OR EQUAL OPERATOR(<=)