AntDB-Oracle Compatibility Developer's Manual P3–74

LEAST

The LEAST function is used to look up the smallest value from any number of expressions.

LEAST(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 LEAST 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.

For example:

1、Query the lowest score of each person's test scores in all subjects.

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, least(chinese, english, math, physical) from test;

2、Compare the size of strings.

SELECT LEAST('HAPPY', 'HAPPEN', 'HAPPINESS')  "Least"  FROM DUAL;

3、Compare the size of numbers.

SELECT LEAST (1, '3.925', '2.4')  "Least"  FROM DUAL;