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