The GREATEST function in Oracle SQL is a simple utility that looks at a list of values you provide and returns the one that is the largest.
It's an incredibly useful function for comparisons within a single row. It works with numbers, text strings, and dates.
What is the GREATEST Function in Oracle?
The GREATEST(value1, value2, ...) function takes one or more expressions and returns the one that is considered the "greatest."
- For Numbers: It returns the largest number.
- For Text: It returns the string that comes last in alphabetical (binary) order.
- For Dates: It returns the latest date.
Don't confuse GREATEST with MAX()!
MAX()is an aggregate function that finds the single highest value in a column across multiple rows.GREATEST()is a scalar function that finds the highest value from a list of arguments within the same row.
GREATEST Function Syntax
The syntax for GREATEST is:
GREATEST(expr1, expr2, ...)
Let's break that down:
expr1, expr2, ...: A comma-separated list of one or more values or columns to compare. The function requires at least one argument.
Oracle determines the data type based on the first argument. It will then try to convert all other arguments to that data type before comparing them.
Oracle GREATEST Function Examples
Here are two practical examples of how to use GREATEST.
Example 1: Finding the Largest Value from a List of Numbers using GREATEST
This is the most common use. The function will look at all the numbers and return the largest one. Note that Oracle is smart enough to implicitly convert text strings (like '3.925') into numbers if the first argument is a number.
Query:
SELECT
GREATEST(1, '3.925', 2.4) AS "Largest_Number"
FROM DUAL;
Result:
Largest_Number
--------------
3.925
Example 2: Finding the "Greatest" String using GREATEST
When used with text, GREATEST returns the value that comes last in alphabetical order. In this comparison, 'HARRY' is "greater" than 'HARRIOT' because, at the fifth character, 'Y' is alphabetically after 'I'.
Query:
SELECT
GREATEST('HARRY', 'HARRIOT', 'HAROLD') AS "Last_Alphabetically"
FROM DUAL;
Result:
Last_Alphabetically
-------------------
HARRY
