In this post, we will discuss the similarities and differences between two Azure SQL functions, MIN and LEAST. Using both functions, we get the least/minimum/lower value out of all the values. They do, however, work in different ways.
The MIN function accepts just one argument, but the LEAST function requires a columns values or a list of comma separated expressions to compare.
If extra columns, in addition to the column given as an input for the MIN function, must be grouped. Outside of the LEAST function, additional columns will be permitted and do not need to be grouped. MIN function can be used with OVER clause but not with LEAST.
Please use the below dataset for practice.
File: Sales.csv
ProductID,2018,2019,2020,2021
P011,151,100,50,77
P012,122,101,44,59
P043,100,65,62,41
P049,121,88,22,80
The following commands were used to create the table and insert the data into it.
CREATE TABLE Sales(
ProductID VARCHAR(20),
Y2018 INT,
Y2019 INT,
Y2020 INT,
Y2021 INT)
INSERT INTO Sales VALUES
(‘P011’,151,100,50,77),
(‘P012’,122,101,44,59),
(‘P043’,100,65,62,41),
(‘P049’,121,88,22,80)
Examples:
SELECT MIN(Y2020) FROM Sales;
This will return the lowest salary from the “emp” table.
SELECT LEAST(Y2020) FROM Sales;
Since the LEAST function requires at least two parameters, the above will result in an error.
SELECT LEAST(Y2020,Y2021) FROM Sales;
The preceding will return the highest values from the supplied two columns.
SELECT MIN(Y2020,Y2021) FROM Sales;
Since the MIN function only accepts one parameter, the above will result in an error.
As stated earlier, if extra columns, in addition to the column given as an input for the MIN function, must be grouped. Outside of the LEAST function, additional columns will be permitted and do not need to be grouped.
SELECT ProductID, MIN(Y2020) FROM Sales GROUP BY ProductID
SELECT ProductID, LEAST(Y2020,Y2021) FROM Sales
Happy learning!!
Big Data and SQL 
2 comments