How to Use GREATEST and LEAST Functions in MySQL

In MySQL, use the GREATEST() function to find the largest value among given expressions, and LEAST() to find the smallest. Both functions compare two or more values and return the maximum or minimum respectively.

Syntax

The GREATEST() and LEAST() functions take two or more arguments and return the highest or lowest value among them.

• GREATEST(value1, value2, ...): Returns the largest value.
• LEAST(value1, value2, ...): Returns the smallest value.

If any argument is NULL, the result is NULL.

sql

GREATEST(value1, value2, ...)
LEAST(value1, value2, ...)

Example

This example shows how to use GREATEST() and LEAST() to find the maximum and minimum values from a set of numbers.

sql

SELECT
  GREATEST(10, 20, 5, 15) AS max_value,
  LEAST(10, 20, 5, 15) AS min_value;

Output

max_value | min_value
----------|----------
20        | 5

Common Pitfalls

One common mistake is not handling NULL values properly. If any argument is NULL, both GREATEST() and LEAST() return NULL. To avoid this, use COALESCE() to replace NULL with a default value.

sql

SELECT
  GREATEST(10, NULL, 5) AS wrong_result,
  GREATEST(10, COALESCE(NULL, 0), 5) AS correct_result;

Output

wrong_result | correct_result
-------------|---------------
NULL         | 10

Quick Reference

Function	Purpose	Returns
GREATEST(value1, value2, ...)	Finds the largest value	Maximum value among arguments
LEAST(value1, value2, ...)	Finds the smallest value	Minimum value among arguments

Key Takeaways

Use GREATEST() to get the highest value among multiple expressions.

Use LEAST() to get the lowest value among multiple expressions.

If any argument is NULL, the result is NULL unless handled with COALESCE().

Both functions require at least two arguments to work properly.

They work with numbers, strings, and dates by comparing values.