MySQL LEAST function

This statement displays the smallest value of the table. The LEAST function returns a null value when the table contains a null value. If the table contains all numerical values, then the function returns the lowest value of the table data.

Syntax

The LEAST function syntax shows below.

LEAST (data1, data2, data3, dataN);

MySQL GREATEST function with the table syntax shows below.

SELECT LEAST (Column1, Column2, Column3, ColumnN) 
FROM table name WHERE condition;

Prerequisite for LEAST comparison function

• Create a new table in the MySQL database.

CREATE TABLE `world`.`crafts` (

  `nos` INT NOT NULL,

  `items` VARCHAR(45) NULL,

  `quantity` INT NULL,

  `available` INT NULL,

  PRIMARY KEY (`nos`));

• Insert value in the table.
• MySQL output command shows table format and its information.

•  mysql> select * from crafts;

Important points of the LEAST function

• The LEAST function compares the multiple column data.
•  This function is used to find out the lowest values of the numerical table data.
• This function returns the last character of the alphabet as the lowest value.
• If a null value is found in the column, the LEAST function returns null data.

Example of the LEAST function

Example1: Execute the below query to know about the "LEAST" statement.

mysql> SELECT LEAST (25, 28, 60, 45, 39);

OUTPUT

The image shows the output as the "lowest" value. If a single value is available, then the "LEAST" function returns it.

Example2: Execute the below query to know how the "LEAST" statement works when it finds duplicate values.

mysql> SELECT LEAST (0, 1, 2, 0, 1, 2);

OUTPUT

The image shows the output as the "lowest" value. This query removes duplicates and displays the single lowest value.

Example3: Execute the below query to see the result when it finds NULL value.

mysql> SELECT LEAST (25, 28, 60, 45, 39, NULL);

OUTPUT

Executing the statement will return the NULL result. It is because the function returns NULL when the specified columns have a NULL value.

Example4: Execute the below query to see how the "LEAST" statement works with the string data.

mysql> SELECT LEAST ("good", "bad", "wow", "perfect", "simple", "better");

OUTPUT

The image shows the output as the "lowest" value. This function returns the first ascending value of the alphabet. Here, the output shows "wow" as the least value.

Example5: The LEAST function with table data example shows below. Here, the crafts table uses three columns to compare the lowest value.

mysql> select least(quantity, available) from crafts;

OUTPUT

Example6: the LEAST function with WHERE clause example shows below.

Execute the below query to know how the "LEAST" statement works with the WHERE clause. Here WHERE clause applies on the "nos" column.

mysql> select least(nos, quantity, available) AS least_value from crafts where nos < 3;

OUTPUT

The output table shows two rows and one column. The image displays the lowest value from the three columns.