LEAST() Function in MySQL

The LEAST() function in MySQL is a versatile tool that returns the smallest (minimum) value from a list of expressions. It can be used with numbers, strings, or even columns of data to find the minimum value according to their data type.

In this article, We will learn about LEAST() Function in MySQL in detail with the help of various examples and so on.

LEAST() Function in MySQL

• The LEAST() function in MySQL returns the smallest (minimum) value from a list of expressions.
• It compares two or more expressions and returns the one with the lowest value according to their data type.

Syntax:

LEAST(X1, X2, X3, ...)

Parameter:

This method accepts N parameter as mentioned above and described below :

• X1, X2, X3…: The list of values from which smallest to be evaluated.

Returns:

It returns the smallest value.

Examples of LEAST() Function in MySQL

Example 1:

Finding the Smallest number between given numbers using LEAST() function.

SELECT LEAST(10, 20, 30, 40) AS Least_Value;

Output:

+-------------+
| Least_Value |
+-------------+
|          10 |
+-------------+

This query selects the smallest value among the numbers `10`, `20`, `30`, and `40` using the `LEAST()` function and returns it as `Least_Value`. The result will be `10`.

Example 2:

Finding Smallest value between given string using LEAST() function.

SELECT LEAST( 'MySQL', 'MS ACCESS', 'SQL') AS LeastValue_String;

Output:

+-------------------+
| LeastValue_String |
+-------------------+
| MS ACCESS         |
+-------------------+

Explanation: This query compares the three strings `’MySQL’`, `’MS ACCESS’`, and `’SQL’` lexicographically and returns the smallest (earliest in alphabetical order) string as `LeastValue_String`. In this case, the result would be `’MS ACCESS’`, since it comes first alphabetically among the three.

Example 3:

The LEAST function can also be used to find the Smallest value of a column data . To demonstrate create a table named.

Student :

CREATE TABLE Student(

    Student_id INT AUTO_INCREMENT,  
    Student_name VARCHAR(100) NOT NULL,
    Student_Class VARCHAR(20) NOT NULL,
    Subject1 INT  NOT NULL,
    Subject2 INT  NOT NULL,
    Subject3 INT  NOT NULL,
    Subject4 INT  NOT NULL,

    PRIMARY KEY(Student_id )

);

Now inserting some data to the Student table :

INSERT INTO  
    Student(Student_name, Student_Class, Subject1, Subject2, Subject3, Subject4)

VALUES
    ('Sayan', 'X', 81, 90, 86, 98 ),
    ('Nitin', 'X', 90, 84, 88, 90 ),
    ('Aniket', 'X', 81, 80, 87, 90 ),
    ('Abdur', 'X', 85, 90, 80, 90  ),
    ('Sanjoy', 'X', 88, 82, 84, 90 ) ;

So, Our table looks like :

+------------+--------------+---------------+----------+----------+----------+----------+
| Student_id | Student_name | Student_Class | Subject1 | Subject2 | Subject3 | Subject4 |
+------------+--------------+---------------+----------+----------+----------+----------+
|          1 | Sayan        | X             |       81 |       90 |       86 |       98 |
|          2 | Nitin        | X             |       90 |       84 |       88 |       90 |
|          3 | Aniket       | X             |       81 |       80 |       87 |       90 |
|          4 | Abdur        | X             |       85 |       90 |       80 |       90 |
|          5 | Sanjoy       | X             |       88 |       82 |       84 |       90 |
+------------+--------------+---------------+----------+----------+----------+----------+

Now, we are going to find least marks for every student among all subjects.

Select 
    Student_id, Student_name, LEAST(Subject1, Subject2, Subject3, Subject4) as Least_Mark 
FROM Student;

Output:

+------------+--------------+------------+
| Student_id | Student_name | Least_Mark |
+------------+--------------+------------+
|          1 | Sayan        |         81 |
|          2 | Nitin        |         84 |
|          3 | Aniket       |         80 |
|          4 | Abdur        |         80 |
|          5 | Sanjoy       |         82 |
+------------+--------------+------------+

This query retrieves the student ID and name from the `Student` table, along with the minimum mark among four subjects (`Subject1`, `Subject2`, `Subject3`, and `Subject4`) for each student, and labels it as `Least_Mark`.

Conclusion

The LEAST() function in MySQL is an efficient and straightforward way to determine the minimum value among multiple expressions or columns. Whether working with numerical data or strings, this function provides a reliable method for quickly finding the smallest value, making it an essential function for data analysis and comparison tasks.

FAQs

Can the LEAST() function compare both numbers and strings in the same query?

No, the LEAST() function should be used with values of the same data type, either all numbers or all strings, to ensure accurate comparisons.

What happens if the LEAST() function encounters NULL values in the list?

If any of the values in the list are NULL, the LEAST() function will return NULL as the result.

Is the LEAST() function case-sensitive when comparing strings?

Yes, the LEAST() function performs case-sensitive comparisons when working with strings, meaning that it considers the case of the letters when determining the smallest value.

https://www.geeksforgeeks.org/least-function-in-mysql/?ref=next_article_top