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:

SELECT LEAST(15,10,25);

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:

SELECT LEAST("Z","M","S");

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.

Code:

SELECT book_name,dt_of_pub,no_page
FROM book_mast
WHERE no_page<LEAST(500,300,395); 

Sample table: book_mast

Output:

Slideshow of MySQL Comparison Function and Operators

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