The SQL GREATEST function and LEAST function are quite useful, but not very well understood. Let’s take a look at these functions and see some examples in this article.
Purpose of the SQL GREATEST and LEAST Function
The SQL GREATEST function returns the “greatest” or largest value in a set of values that you provide to it.
The SQL LEAST function returns the “least” or smallest value in a set of values that you provide to it, and it’s the opposite of the GREATEST function.
In both functions, you can use numbers as well as text values.
SQL GREATEST Function Syntax and Parameters
The syntax for the SQL GREATEST function is:
GREATEST ( expr1, [expr_n] )
The parameters of the function are:
- expr1 (mandatory): This is the first expression to use for comparison.
- expr_n (optional): This is one or more expressions, separated by commas, to use in a comparison.
SQL LEAST Function Syntax and Parameters
The syntax for the SQL LEAST function is:
LEAST ( expr1, [expr_n] )
The parameters of this function are:
- expr1 (mandatory): This is the first expression to use for comparison.
- expr_n (optional): This is one or more expressions, separated by commas, to use in a comparison.
How GREATEST Is Calculated
The calculation for GREATEST is pretty simple. It’s similar to how LEAST is calculated.
If the expressions are numeric, the GREATEST function finds the largest number, whereas the LEAST function finds the smallest number.
If the expressions are characters, the GREATEST function finds the last value if they were sorted alphabetically. A character is considered larger than another character if it has a higher character set value. The LEAST function finds the earliest value if they were sorted alphabetically. A character is considered smaller than another character if it has a lower character set value.
See the examples section below for more information on how GREATEST and LEAST work.
What Datatype Is Returned By SQL GREATEST or Oracle LEAST?
The SQL GREATEST and LEAST functions return datatype depends on a few factors:
- If the data types of the expressions are different, GREATEST will convert them to the same data type as expr1.
- All of the expressions are converted to the same data type before the first comparison is done.
- If any of the expressions are NULL, then GREATEST will return NULL.
- The return data type is VARCHAR2 if all values are character values.
Can You Use GREATEST or LEAST with a DATE?
Yes, you can. It works in a similar way to other data types. It finds the earliest occurring date.
See the Examples section below for an example that uses DATE fields.
What Is The Difference Between SQL GREATEST and MAX?
There are a few differences between the SQL GREATEST and MAX functions:
- Both MAX and GREATEST will return one result, but MAX is an aggregate function and GREATEST is not.
- GREATEST can return multiple rows. It will return one row for each row of data you query as it is not an aggregate function.
So, GREATEST can be used to go across the row, and MAX can be used to go down the columns, or compare values in the same column in different rows.
What Is The Difference Between SQL LEAST and MIN?
There are a few differences between the SQL LEAST and MIN functions:
- Both MIN and LEAST will return one result, but MIN is an aggregate function and LEAST is not.
- LEAST can return multiple rows. It will return one row for each row of data you query as it is not an aggregate function.
So, just like GREATEST mentioned above, LEAST can be used to go across the row, and MIN can be used to go down the columns, or compare values in the same column in different rows.
Examples of the GREATEST Function and LEAST Function
Here are some examples of the GREATEST and LEAST functions.
Example 1
This example uses a few numbers.
SELECT
GREATEST(5, 18, 21, 3, 65) AS GREATEST_CHECK,
LEAST(5, 18, 21, 3, 65) AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| 65 | 3 |
It shows the number 65 as that is the largest number in the list, and 3 as it’s the smallest number.
Example 2
This example uses a few strings.
SELECT
GREATEST(‘Fred’, ‘Edward’, ‘Ruby’, ‘Jane’) AS GREATEST_CHECK,
LEAST(‘Fred’, ‘Edward’, ‘Ruby’, ‘Jane’) AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| Ruby | Edward |
The GREATEST function shows Ruby because the letter R comes before any of the other first characters, and LEAST shows Edward as E comes before F, R and J.
Example 3
This example uses number values inside quotes, to be treated as strings.
SELECT
GREATEST('9', '12', '73', '15') AS GREATEST_CHECK,
LEAST('9', '12', '73', '15') AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| 9 | 12 |
For GREATEST, the result is 9. This is because the first character of 9 comes after any of the other first characters from other values. Then the comparison is done on the second character.
For LEAST, the result is 12. This is because the first character of 1 comes before any of the other values, except 15. Then the comparison is done on the second character.
Example 4
This example uses a mix of data types.
SELECT
GREATEST('45', 'apple', 'banana', 100) AS GREATEST_CHECK,
LEAST('45', 'apple', 'banana', 100) AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| banana | 100 |
The result of GREATEST is banana because it is the last character if the list is sorted.
The result of LEAST is 100 because it is less than both strings, as well as the ’45’ stored as a string.
Example 5
This example uses several dates.
SELECT
GREATEST(
TO_DATE('25-MAR-2022'),
TO_DATE('14-APR-2022’),
TO_DATE('7-JAN-2022')
) AS GREATEST_CHECK,
LEAST(
TO_DATE('25-MAR-2022'),
TO_DATE('14-APR-2022'),
TO_DATE('7-JAN-2022')
) AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| 14-APR-2022 | 7-JAN-2022 |
GREATEST shows the date from April as it occurs later, while LEAST shows the earliest date from January.
Example 6
This example uses a NULL value in the list of values.
SELECT
GREATEST('Fred', 'Edward', NULL, 'Jane') AS GREATEST_CHECK,
LEAST('Fred', 'Edward', NULL, 'Jane') AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| (null) | (null) |
The result is NULL for both columns because there is a NULL value in the list of values.
Example 7
This example uses a long list of values to see how it is treated.
SELECT
GREATEST('x', 'w', 'y', 'a', 'q', 'u', 'i', 'a', 'd',
's', 'g', 'k', 'e', 'w', 'i', 'p', 'a', 'z', 'b',
'c', 'm', 'w', 'r', 'y', 'n')
AS GREATEST_CHECK,
LEAST('x', 'w', 'y', 'a', 'q', 'u', 'i', 'a', 'd',
's', 'g', 'k', 'e', 'w', 'i', 'p', 'a', 'z', 'b',
'c', 'm', 'w', 'r', 'y', 'n')
AS LEAST_CHECK;Result:
| GREATEST_CHECK | LEAST_CHECK |
| z | a |
The result is ‘z’ as it is the largest value for GREATEST, and ‘a’ for LEAST as it is the smallest.
Similar Functions
Some functions which are similar to the GREATEST and LEAST functions are:
- MAX: Finds the highest value in a set of values
- MIN: Finds the lowest value in a set of values
You can fund a full list of Oracle functions here.