top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is the difference between MAX () and GREATEST () Functions in Oracle?

+2 votes
3,665 views
What is the difference between MAX () and GREATEST () Functions in Oracle?
posted Aug 12, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+2 votes
 
Best answer

MAX is an aggregate function which takes only one column name of a table as parameter whereas
Greatest is a general function which can take any number of values and column names from dual and
table respectively.

answer Aug 13, 2015 by Arun Gowda
0 votes

GREATEST is not to be used on a singular item. Although it will return values with single item, as input the effect is not useful. Use GREATEST when you
need to get the greatest values from a list of values. MAX on the other hand will return the max value on a column.

Here is what the documentation gives :

GREATEST

GREATEST returns the greatest of the list of exprs. All exprs. after the first are implicitly converted to the datatype of the first expr. before the comparison. Oracle compares the exprs. using nonpadded comparison
semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher
character set value. If the value returned by this function is character data, its datatype is always VARCHAR2.

Example:

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')
"Greatest" FROM DUAL;

Greatest

HARRY

MAX

MAX returns maximum value of expr. You can use it as an aggregate or analytic function. If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

answer Aug 13, 2015 by Manikandan J
...