FB

The Oracle 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 Oracle GREATEST and LEAST Function

The Oracle GREATEST function returns the “greatest” or largest value in a set of values that you provide to it.Oracle GREATEST and LEAST Functions

The Oracle 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.

 

Oracle GREATEST Function Syntax and Parameters

The syntax for the Oracle 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.

 

Oracle LEAST Function Syntax and Parameters

The syntax for the Oracle 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

 

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 works.

 

What Datatype Is Returned By Oracle GREATEST or Oracle LEAST?

The Oracle 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 Oracle GREATEST and MAX?

There are a few differences between the Oracle 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, basically 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 Oracle LEAST and MIN?

There are a few differences between the Oracle 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 abov, 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 function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

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

FROM dual;

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

FROM dual;

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

FROM dual;

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

FROM dual;

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-2017′),

 TO_DATE(’14-APR-2017’),

 TO_DATE(‘7-JAN-2017’)

AS GREATEST_CHECK,

LEAST(

 TO_DATE(’25-MAR-2017′),

 TO_DATE(’14-APR-2017′),

 TO_DATE(‘7-JAN-2017’)

) AS LEAST_CHECK

FROM dual;

Result:

GREATEST_CHECK LEAST_CHECK
14-APR-2017 7-JAN-2017

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

FROM dual;

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

FROM dual;

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 function 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Get Your FREE PDF: 9 Ways to Improve your Database Skills