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:

2023-12-19, 290🔥, 0💬