Greatest and least of ORACLE built-in functions

Oracle compares the maximum or minimum value of a column, and we use the Max and Min functions without thinking, but for the maximum or minimum of a row? is not the daily use of less, many people do not know that there are also built-in functions to implement this function: Coalesce/greatest/least.

1. Coalesce returns the first non-null value of the expression list.

Format: Coalesce (value1, value2, Value3, ...)

Meaning: Returns the first non-null value of the list of values.

The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared.

Example:select COALESCE (1, NULL, 2) from dual; -Return 1

Select COALESCE (NULL, 2, 1) from dual; -Return 2

Select COALESCE (T.empno, t.mgr) from Scott.emp t; -Effect similar to NVL (T.empno, T.mgr)

2. Greatest maximum value in the return value list

Format: Greatest (value1, value2, Value3, ...)

Meaning: Returns the maximum value for the list of values.

The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared.

A null value is returned when one of the value values in the list is null.

Example:select Greatest (1, 3, 2) from dual; -Return 3

Select Greatest (' A ', ' B ', ' C ') from dual; -Return C

Select Greatest (NULL, ' B ', ' C ') from dual; -Return NULL

Select Greatest (T.empno, t.mgr) from Scott.emp t; -Return empno and MGR larger values

3. LEAST the minimum value in the return value list

Format: LEAST (value1, value2, Value3, ...)

Meaning: Returns the smallest value in the value list.

The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared.

A null value is returned when one of the value values in the list is null.

Example:select least (1, 3, 2) from dual; -Return 1

Select least (' A ', ' B ', ' C ') from dual; -Return a

Select least (NULL, ' B ', ' C ') from dual; -Return NULL

Select least (T.empno, t.mgr) from Scott.emp t; -Return empno and MGR smaller values

Greatest and least of ORACLE built-in functions