The MySQL LEAST() function is a built-in function that you can use to find the smallest value from all the arguments you passed into the function.
The syntax of LEAST() function is as shown below:
LEAST(argument, argument, ...)
To work properly, the LEAST() function must be called with at 2 arguments at the minimum:
SELECT LEAST(1, 2);
-- +-------------+
-- | LEAST(1, 2) |
-- +-------------+
-- | 1 |
-- +-------------+
Passing less than 2 arguments will cause MySQL to throw an error as shown below:
SELECT LEAST(2);
-- ERROR 1582 (42000): Incorrect parameter count
-- in the call to native function 'LEAST'
The LEAST() function will compare integer values as integers. If there’s at least a single FLOAT or DECIMAL value passed to the function, then the whole arguments will be compared using the type of that single value.
SELECT LEAST(12.5, 5);
-- +----------------+
-- | LEAST(12.5, 5) |
-- +----------------+
-- | 5.0 |
-- +----------------+
SELECT LEAST(10.99, 5);
-- +-----------------+
-- | LEAST(10.99, 5) |
-- +-----------------+
-- | 5.00 |
-- +-----------------+
As you can see from the example above, even though 5 is an integer value, it’s returned with an extra floating point.
Although numerical values are the most intuitive values to pass as arguments to the function, you can also pass string values into the function.
The following example compares the alphabet letters and returns the smallest one:
SELECT LEAST('z', 'd', 'c');
-- +----------------------+
-- | LEAST('z', 'd', 'c') |
-- +----------------------+
-- | c |
-- +----------------------+
Since the LEAST() function requires at least two arguments, the function can’t be used to find the smallest value from a single table column.
For example, suppose you have a students column with the following data:
+----+-------+-------+------+
| id | name | score | age |
+----+-------+-------+------+
| 1 | Jack | 72 | 19 |
| 2 | Susan | 48 | 17 |
| 3 | John | 56 | 19 |
| 4 | Fany | 81 | 18 |
+----+-------+-------+------+
You can find the minimum value from the score column like this:
SELECT LEAST(score) FROM students;
ERROR 1582 (42000): Incorrect parameter count
in the call to native function 'LEAST'
What you can do is compare the value of score and age and return the smallest of the two:
SELECT LEAST(score, age) FROM students;
-- +-------------------+
-- | LEAST(score, age) |
-- +-------------------+
-- | 19 |
-- | 17 |
-- | 19 |
-- | 18 |
-- +-------------------+
To find the smallest value from a single column, you need to use the MIN() function instead of the LEAST() function.
Learn more: How MySQL MIN() function works
You’ve learned how the LEAST() function works in MySQL. Nice work! 👍
