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 –

### 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() 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 –

### 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 –

### 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 –

### Working With Tables

Consider the below ‘RightAngledTriangle’ table.

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 –

## 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

- MySQL official documentation on
`GREATEST()`

and`LEAST()`

functions.