LEAST Function in SQL

The LEAST is a SQL numeric function which shows the least value from the specified inputs in Structured Query Language.

Syntax of LEAST Function

SELECT LEAST(Number1, Number2, Number3, Number4, ……, NumberN) AS Alias_Name;

In the LEAST syntax, we have to pass those numbers from which we want to find least value.

In the Structured Query Language, we can also use the LEAST function with the column of the table as shown in the following block:

SELECT LEAST(Integer_column_Name1, Integer_column_Name1, Integer_column_Name1, Integer_column_Name1, ……., Integer_column_NameN,) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and columns of that table on which we want to perform the LEAST function.

Examples of LEAST function

Example 1: This example returns the least from the specified numbers:

Output:

least_of_numbers
1

Example 2: This example returns the least from the specified numbers:

Output:

least_of_numbers
22

Example 3: This example returns the least value from the given inputs:

Output:

least_of_numbers
3

Example 4: This example returns the least representation of 255:

Output:

least_of_numbers
3.08

Example 5: This example returns the least value:

Output:

least_of_numbers
Shows Error

Example 6: This example uses the LEAST function with the SQL table.

In this example, we will create the new table through which we will perform the LEAST function on the columns of the table:

The following shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
.......,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

CREATE TABLE Product_Details
(
Product_ID INT NOT NULL,
Product_Name Varchar(50),
Product_Quantity INT,
Purchasing_Price INT,
Selling_Price INT,
Release_Date Date, 
Product_Rating INT
);

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

Product_ID	Product_Name	Product_Quantity	Purchasing_Price	Selling_Price	Release_Date	Product_Rating
104	P1	10	945	NULL	2022-04-30	NULL
202	P4	15	45	75	2022-01-28	5
103	P2	18	25	NULL	2022-02-18	4
111	P7	25	5	15	2021-12-25	9
210	P6	15	50	70	2021-10-15	NULL
212	P8	19	110	250	2022-01-28	4
112	P10	10	550	835	2022-04-11	NULL

Query 1: The following SELECT query uses the LEAST function with the Product_Quantity and Product_ID column of the above Product_Details table:

SELECT Product_ID, Product_Quantity, LEAST(Product_ID, Product_Quantity) AS least_of_Product_ID_Quantity FROM Product_Details

This query shows the least value from the product id and quantity of each product.

Output:

Product_ID	Product_Quantity	least_of_Product_ID_quantity
104	10.250	10.250
202	15.500	15.500
103	18.250	18.250
111	25.250	25.250
210	15.500	15.500
212	19.750	19.750
112	10.250	10.250

Query 2: The following SELECT query uses the LEAST function with Selling_Price and Purchasing_Price column of the above Product_Details table:

SELECT Selling_Price, Purchasing_Price LEAST(Selling_Price, Purchasing_Price) AS least_of_Selling_Purchasing_Price FROM Product_Details;

This query shows the least value from purchasing and selling price of each product.

Output:

Selling_Price	Purchasing_Price	least_of_Selling_PurchasingPrice
NULL	945	-
75	45	45
NULL	25	-
15	5	5
70	50	50
250	110	110
835	550	550

Query 3: The following SELECT query uses the LEAST function with the Purchasing_Price and Product_ID column of the above Product_Details table:

SELECT Product_ID, Purchasing_Price, LEAST(Product_ID, Purchasing_Price) AS least_of_Product_ID_and_Purcasing FROM Product_Details;

This query shows the least value from the product id and purchasing price of each product.

Output:

Product_ID	Purchasing_Price	least_of_Product_ID_and_Purchasing
104	945	104
202	45	45
103	25	25
111	5	5
210	50	50
212	110	110
112	550	112

LEAST Function with WHERE Clause

In the Structured Query Language, we can also use the LEAST function with the WHERE clause in the SELECT statement.

Syntax of LEAST Function with WHERE Clause

SELECT LEAST(Integer_column_Name1, Integer_column_Name1, Integer_column_Name1, Integer_column_Name1, ……., Integer_column_NameN) AS Alias_Name FROM Table_Name WHERE [condition];

In this syntax, we have to sepcify the condition in the WHERE clause for performing least function on the filtered rows.

Example of LEAST Function with WHERE Clause

Let's take the above Product_Details table to understand the where clause with least function.

Query: The following SELECT query uses the WHERE Clause with LEAST function on the Product_Quantity and Product_ID columns of the above Product_Details table:

SELECT Product_ID, Product_Quantity, LEAST(Product_ID, Product_Quantity) AS least_of_Product_ID_Quantity FROM Product_Details WHERE Product_Quantity > 10.250;

This query shows the least value from product id and quantity of those products whose quantity is greater than 10.250.

Output:

Product_ID	Product_Quantity	least_of_Product_ID_quantity
202	15.500	15.500
103	18.250	18.250
111	25.250	25.250
210	15.500	15.500
212	19.750	19.750

LEAST Function with SUM function

In the Structured Query Language, we can also use the LEAST function with the SUM SQL function in the SELECT statement.

Syntax of LEAST Function with WHERE Clause

SELECT LEAST(SUM(Integer_column_Name1), SUM(Integer_column_Name2), SUM(Integer_column_Name3), SUM(Integer_column_Name4), ……., SUM(Integer_column_NameN)) AS Alias_Name FROM Table_Name;

Example of LEAST Function with WHERE Clause

Let's take the above Product_Details table to understand the sum function with the least function.

Query: The following SELECT query uses the SUM function with LEAST function on the Product_Quantity and Product_ID columns of the above Product_Details table:

SELECT Product_ID, Product_Quantity, LEAST(SUM(Product_ID), SUM(Product_Quantity)) AS least_of_SUM_Product_ID_Quantity FROM Product_Details WHERE Product_Quantity > 10.250;

This query shows the least value from sum of product id and quantity.

Output: