LEAST() Function in MySQL

LEAST() function in MySQL is used to find smallest values from given arguments respectively. If any given value is NULL, it return NULLs. Otherwise it returns the smallest value.

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.

Example-1 : Finding Smallest number between given numbers using LEAST() function.

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

Output :

+-------------+
| Least_Value |
+-------------+
|          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         |
+-------------------+

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 |
+------------+--------------+------------+