MySQL GREATEST() and LEAST()

By /
GREATEST And LEAST Functions

In this tutorial, we will study the MySQL GREATEST() and LEAST() functions. Suppose you have a ‘Triangles’ table that stores the length of the three sides of each triangle in three separate columns. You have been tasked to find the longest and the shortest side of each triangle. How do you do this? Normally, you could use functions like MIN() and MAX() for this. However, you are comparing values across multiple columns. MIN() and MAX() do not support that. They compare values only in one column. This is where the MySQL GREATEST() and LEAST() functions come into play.

  • MySQL GREATEST() returns the greatest value out of a list of arguments.
  • MySQL LEAST() returns the smallest value out of a list of arguments.

Let us explore the syntax and examples of both these functions.

Syntax of MySQL GREATEST()

GREATEST(arg1, arg2, arg3, ...)Code language: SQL (Structured Query Language) (sql)

Where, ‘arg1’, ‘arg2’ and ‘arg3’ are a list of arguments from which the greatest value should be returned.

Syntax of MySQL LEAST()

LEAST(arg1, arg2, arg3, ...)Code language: SQL (Structured Query Language) (sql)

Where, ‘arg1’, ‘arg2’ and ‘arg3’ are a list of arguments from which the smallest value should be returned.

Examples of MySQL GREATEST() and LEAST()

Let us take a look at the examples now.

MySQL GREATEST() And LEAST() With Numbers

Let us start off by finding the greatest and the smallest value from the following list of numbers – 25,14,7,12. We will use the SELECT statement and an alias called ‘Result’ for our output. The queries are –

SELECT GREATEST(25,14,7,12) AS Result; 
SELECT LEAST(25,14,7,12) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Greatest Least Numbers1

MySQL GREATEST() And LEAST() With Floating-Point Values

Similarly, we can pass floating type values in the GREATEST() and LEAST() functions. Let us find the greatest and the least values in the list – 7.75, 2, 56, 98.23,45.223, 14. The queries are –

SELECT GREATEST(7.75, 2, 56, 98.23,45.223, 14) AS Result; 
SELECT LEAST(7.75, 2, 56, 98.23,45.223, 14) AS Result;Code language: SQL (Structured Query Language) (sql)

If there is a single floating-point number in the list, GREATEST() and LEAST() treat all the numbers as floating-point values. This is evident by the output below –

Greatest Least Numbers2

GREATEST() And LEAST() With Letters

We can also use the GREATEST() and LEAST() functions to find the greatest and smallest alphabet values. Consider the below queries.

SELECT GREATEST('r', 'a', 'c', 'v', 'e') AS Result; 
SELECT LEAST('r', 'a', 'c', 'v', 'e') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Greatest Least Alphabets

GREATEST() And LEAST() With Words

Similarly, we can also use GREATEST() and LEAST() functions to find the greatest and smallest values among words. Let us see an example of this. Consider the below queries.

SELECT GREATEST('Dog', 'cat', 'tiger', 'fox', 'anaconda') AS Result; 
SELECT LEAST('Dog', 'cat', 'tiger', 'fox', 'anaconda') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Greatest Least Words

GREATEST() and LEAST() With NULL Values

GREATEST() and LEAST() functions return NULL if we pass a NULL value in the list of arguments. This can be demonstrated by the below example.

SELECT GREATEST('r', 'a', 'c', 'v', NULL) AS Result; 
SELECT LEAST('r', 'a', 'c', 'v', NULL) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Greatest MySQL Least Null

Working With Tables

Consider the below ‘RightAngledTriangle’ table.

Greatest Least Rightangledtriangle

Let us display the greatest and the smallest side of each right-angled triangle using the GREATEST() and the LEAST() functions. For this, we will pass the Base, Height, and Hypotenuse column as the list of arguments. The query is –

SELECT FigureId, LEAST(Base, Height, Hypotenuse) AS 'Smallest Side', 
GREATEST(Base, Height, Hypotenuse) AS 'Longest Side' 
FROM RightAngledTriangle;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Greatest Least Table Example

Conclusion

Comparing values from different columns is a very important operation, especially in data analysis. This is why functions like GREATEST() and LEAST() are important. I would highly recommend you to check out the below reference links.

References