Forgotten SQL Function: LEAST

At least I forgot all about LEAST, a very simple Oracle SQL function.

You want to select the smallest value from a list of expressions, you use LEAST:

LEAST(expr [, expr ]…)

Some examples: 

select least (33, 23, 10, 7) as least_value
from dual

LEAST_VALUE            
---------------------- 
7                      

1 rows selected


select least ('hoola', 'hoop', 'hoopla') as least_value
from dual

LEAST_VALUE 
----------- 
hoola       

1 rows selected


select least ('.', '2', 3, 'a') as least_value
from dual

LEAST_VALUE 
----------- 
.           

1 rows selected

When comparing values, Oracle uses nonpadded comparison semantics. With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater.

If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater.

If two values of equal length have no differing characters, then the values are considered equal.

Another example:

select least (1, '2', 3, 'a') as least_value
from dual

SQL Error: ORA-01722: invalid number

Oops! an error. Why? well, because Oracle implicitly converts the datatype of all expressions in the list to the datatype of the first expression before the comparison. The datatype of the first expression in the example above is number. When Oracle tries to convert ‘a’ to number it fails and returns ORA-01722.

Related Oracle Documentation

Possibly related:

Tagged , | Post a Comment