MySQL Select minimum/maximum among two (or more) given values

In MySQL, you can use the LEAST and GREATEST functions to determine the minimum or maximum value among two or more given values.

Example 1: Using the LEAST Function

The LEAST function returns the smallest value among all specified parameters. For instance, to identify the smallest value among several numbers, you can use it as follows:

sql
SELECT LEAST(34, 7, 23, 99, 15) AS smallest_value;

This SQL statement returns 7 because 7 is the smallest value among 34, 7, 23, 99, and 15.

Example 2: Using the GREATEST Function

The GREATEST function, which serves as the counterpart to LEAST, returns the largest value among all specified parameters. For example, to find the largest value among several numbers, you can use it as follows:

sql
SELECT GREATEST(34, 7, 23, 99, 15) AS largest_value;

This SQL statement returns 99 because 99 is the largest value among 34, 7, 23, 99, and 15.

Practical Application

Suppose you have an orders table with columns price and discount_price. To calculate the actual minimum payment amount for customers, you can use the LEAST function to compare the original price and the discounted price:

sql
SELECT order_id, LEAST(price, discount_price) AS minimum_payment
FROM orders;

This query returns the actual minimum payment amount for each order by comparing the values of price and discount_price.

With these functions, MySQL provides a straightforward and efficient method for handling comparisons among multiple values, enabling quick results for both minimum and maximum values. This approach is highly valuable in data analysis and routine database operations.