LEAST() - Finding the Least/Minimum Value

Q

How to find the least (minimum) value of a given list of values using the LEAST() function?

✍: FYIcenter.com

A

LEAST(val1, val2, ...) is a MySQL built-in function that returns the least (minimum) value of a given list of values. For example:

SELECT LEAST(70, 89, 73, 99, 101, 110, 116, 101, 114);
  -- +------------------------------------------------+
  -- | LEAST(70, 89, 73, 99, 101, 110, 116, 101, 114) |
  -- +------------------------------------------------+
  -- |                                             70 |
  -- +------------------------------------------------+

SELECT LEAST('Grape', 'Orange', 'Banana', 'Apple');
  -- +---------------------------------------------+
  -- | LEAST('Grape', 'Orange', 'Banana', 'Apple') |
  -- +---------------------------------------------+
  -- | Apple                                       |
  -- +---------------------------------------------+

If the arguments comprise a mix of multiple data types, LEAST() converts them to the same data type using the following rules:

  • If any argument is NULL, the result is NULL. No comparison is needed.
  • Boolean values of TRUE and FALSE are compared as integers of 1 and 0.
  • If all arguments are integers, they are compared as integers.
  • If all arguments are numbers with at least one argument is double precision, they are compared as double-precision values.
  • If all arguments are numbers with at least one argument is a DECIMAL value, they are compared as DECIMAL values.
  • If all arguments are numbers with single-precision, they are compared as single-precision values.
  • If the arguments comprise a mix of numbers and strings, they are compared as strings (character or byte sequences).
  • If any argument is a character sequence, the arguments are compared as character sequences.
  • In all other cases, the arguments are compared as binary sequences.

Here are examples with arguments of mix data types:

SELECT LEAST(70, 89, 73, 99, 101, 'Banana', 'Apple');
  -- +-----------------------------------------------+
  -- | LEAST(70, 89, 73, 99, 101, 'Banana', 'Apple') |
  -- +-----------------------------------------------+
  -- | 101                                           |
  -- +-----------------------------------------------+

SELECT LEAST(TRUE, 70, 89, NULL, 'Banana', 'Apple');
  -- +----------------------------------------------+
  -- | LEAST(TRUE, 70, 89, NULL, 'Banana', 'Apple') |
  -- +----------------------------------------------+
  -- | NULL                                         |
  -- +----------------------------------------------+

SELECT LEAST(NOW(), 'Yesterday'), LEAST('2023-12-16', 'Yesterday');
  -- +---------------------------+----------------------------------+
  -- | LEAST(NOW(), 'Yesterday') | LEAST('2023-12-16', 'Yesterday') |
  -- +---------------------------+----------------------------------+
  -- | 2023-12-16 20:35:34       | 2023-12-16                       |
  -- +---------------------------+----------------------------------+

SELECT LEAST(NOW(), '2099-12-01', CURRENT_TIMESTAMP());
  -- +-------------------------------------------------+
  -- | LEAST(NOW(), '2099-12-01', CURRENT_TIMESTAMP()) |
  -- +-------------------------------------------------+
  -- | 2023-12-16 20:36:43                             |
  -- +-------------------------------------------------+

SELECT LEAST(JSON_ARRAY(70, 89), JSON_ARRAY('70', '89'));
  -- +---------------------------------------------------+
  -- | LEAST(JSON_ARRAY(70, 89), JSON_ARRAY('70', '89')) |
  -- +---------------------------------------------------+
  -- | ["70", "89"]                                      |
  -- +---------------------------------------------------+

Reference information of the LEAST() function:

LEAST(val1, val2, ...): min
  Returns the least (minimum) value of a given list of values.

Arguments, return value and availability:
  val1, val2, ...: Two or more values to be examined.
  min: Return value. The minimum value of multiple values.
  Available since MySQL 4.0.

Related MySQL functions:

 

NAME_CONST() - PS Thread ID of Given Connect

ISNULL() - Detecting NULL Value

MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References

2023-12-19, 290🔥, 0💬