AntDB-Oracle Compatibility Developer’s Manual P3–73

Generic comparison functions

GREATEST

The GREATEST function is used to look up the largest value from any number of expressions.

GREATEST(value [, value2 ] ... )

Expressions must all be convertible to a common data type, which is the type of the result. Null values in the list are ignored. The final result is null only if all expressions compute null values.

Note that the function GREATEST is not reflected in the SQL standard, but is a currently common extension function.

Compare:

MAX and MIN are to find the maximum and minimum values on a group of a column of a table. While GREATEST and LEAST can find the maximum and minimum values in a row.

Example:

Check the highest score of all subjects in each person's exam results.

create table test(id int, name varchar2(10), chinese number, english number, math number, physical number);
insert into test values(1,'Tom', 91, 89, 69, 88);
insert into test values(2,'Alice', 88, 81, 77, 100);
select name, greatest(chinese, english, math, physical) from test;

1、Compare the size of strings.

SELECT GREATEST('HAPPY', 'HAPPEN', 'HAPPINESS') "Greatest" FROM DUAL;

2、Compare the size of numbers.

SELECT GREATEST (1, '3.935', '2.4') "Greatest" FROM DUAL;