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 articles:
Filed in Oracle on 14 Mar 06 | Tags: function, sql
