It's important to understand the differences between MySQL MIN function and LEAST function.

• MIN function compares rows within a column to find the minimum value. It compares vertically.
• MIN function requires the use of GROUP BY clause.
• LEAST function compares data across a list of arguments (values and/or columns). It compares horizontally.
• LEAST function does not require the use of GROUP BY clause.

When using LEAST function, please consider the following rules:

1. It returns NULL if any argument is NULL.
2. If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
3. If the return value is used in a REAL/FLOAT context or all arguments are real-valued, they are compared as reals.
4. If the arguments comprise a mix of numbers and strings, they are compared as strings.
5. If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
6. In all other cases, the arguments are compared as case-insensitive strings.
7. The arguments can have mixed data types. It can mix actual values and column names of a table.
8. If two columns contain the same smallest values, the LEAST function returns the value in the first encountered column based on the order of columns in table structure.

Syntax:

LEAST(value1, value2, value3, ...)

or

LEAST(column1, column2, column3, ...)

or

LEAST(value1, value2, value3, column1, column2, column3, ...)

As shown above, the arguments can mix actual values and column names of a table.

Create the sample table for this tutorial

To demonstrate the LEAST function, we need to create a table called quarterly_sales which contains quarterly sales amounts for each product in 1997 in Northwind database. We'll use this table to do our tutorial queries.

create table quarterly_sales 
as
select a.ProductName, 
    round(sum(case quarter(c.OrderDate) when '1' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr1,
    round(sum(case quarter(c.OrderDate) when '2' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr2,
    round(sum(case quarter(c.OrderDate) when '3' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr3,
    round(sum(case quarter(c.OrderDate) when '4' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr4 
from Products a 
inner join Order_Details b on a.ProductID = b.ProductID
inner join Orders c on c.OrderID = b.OrderID
inner join Customers d on d.CustomerID = c.CustomerID 
where c.OrderDate between date('1997-01-01') and date('1997-12-31')
group by a.ProductName
order by a.ProductName, d.CompanyName;

The table created contains 77 products with Quarter 1 to Quarter 4 sales figures.

Practice 1:

The query below returns the smallest value in the arguments based on what the context the list is in.

SELECT LEAST(2, 5, 0) AS integer_context,
       LEAST(2, 5, 0, null) AS integer_context,
       LEAST(34.0, 3.0, 5.0, 767.0) AS real_context,
       LEAST('B', 'a', 'c', 'd') AS case_insensitive_context,
       LEAST('B', 'a', 5, 7, 'c', 'd') AS mixed_context;

1 row returned:

Practice 2: Case sensitive string comparison.

The two queries below instruct MySQL to perform case-sensitive string comparison by using COLLATE operator with utf8mb4_0900_as_cs or utf8mb4_bin collation.

Please note: utf8mb4_bin and utf8mb4_0900_as_cs have the same effect - bin is short for binary and _cs stands for case-sensitive.

SELECT LEAST('B', 'b', 'c', 'C') COLLATE utf8mb4_bin AS case_sensitive_context;

SELECT LEAST('B', 'b', 'c', 'C') COLLATE utf8mb4_0900_as_cs AS case_sensitive_context;

1 row returned:

Practice 3: Compare data across multiple columns

This query compares sales figures across the four quarters for each product and return the minimum sales amount.

select ProductName, 
       LEAST(qtr1, qtr2, qtr3, qtr4) as Min_quarterly_sales 
from quarterly_sales;

77 rows returned:

Practice 4: Compare data across multiple columns and indicate which column has the smallest value

This query does the same thing as the query above, but in addition, it also works out which quarter has the smallest sales.

Please note that if two columns contain the same smallest values, the LEAST function returns the value in the first encountered column based on the order of columns in table structure. For example, product Chef Anton's Gumbo Mix has both 0 sales in Quarter 1 and Quarter 2, but Quarter 1 is returned in the result (see Min_Quarter column).

select ProductName, 
	   LEAST(qtr1, qtr2, qtr3, qtr4) as Min_quarterly_sales,
	   case when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr1 then 'Qtr 1'
                when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr2 then 'Qtr 2'
                when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr3 then 'Qtr 3'
       else 'Qtr 4'
       end as Min_Quarter
from quarterly_sales;

77 rows returned:

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

17. How to make case-sensitive comparison in MySQL

18. Use GREATEST function to find the LARGEST value from multiple arguments

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page