Skip to main content

LEAST (SQL)

A function that returns the least value from a series of expressions.

Synopsis

LEAST(expression,expression[,...])

Description

LEAST returns the smallest (least) value from a comma-separated series of expressions. Expressions are evaluated in left-to-right order. If only one expression is provided, LEAST returns that value. If any expression is NULL, LEAST returns NULL.

If all of the expression values resolve to canonical numbers, they are compared in numeric order. If a quoted string contains a number in canonical format, it is compared in numeric order. However, if a quoted string contains a number not in canonical format (for example, '00', '0.4', or '+4'), it is compared as a string. Note that canonical numbers that are right-padded with zeroes (for example, '125.2500') are compared in numeric order as well. String comparisons are performed character-by-character in collation order. Any string value is greater than any numeric value.

The empty string is greater than any numeric value, but less than any other string value.

If the returned value is a number, LEAST returns it in canonical format (leading and trailing zeros removed, etc.). If the returned value is a string, LEAST returns it unchanged, including any leading or trailing blanks.

LEAST returns the least value from a comma-separated series of expressions. GREATEST returns the greatest value from a comma-separated series of expressions. COALESCE returns the first non-NULL value from a comma-separated series of expressions.

Data Type of Returned Value

If the data types of the expression values are different, the data type returned is the type most compatible with all of the possible return values, the data type with the highest data type precedence. For example, if one expression is an integer and another expression is a fractional number, LEAST returns a value with data type NUMERIC. This is because NUMERIC is the data type with the highest precedence that is compatible with both. If, however, an expression is a literal number or string, LEAST returns data type VARCHAR.

Arguments

expression

An expression that resolves to a number or a string. The values of these expressions are compared to each other and the least value returned. An expression can be a field name, a literal, an arithmetic expression, a host variable, or an object reference. You can list up to 140 comma-separated expressions.

Examples

In the following example, each LEAST compares three canonical numbers:

SELECT LEAST(22,2.2,-21) AS HighNum,
       LEAST('2.2','22','-21') AS HighNumStr

In the following example, each LEAST compare three numeric strings. However, each LEAST contains one string that is non-canonical; these non-canonical values are compared as character strings. A character string is always greater than a number:

SELECT LEAST('22','+2.2','21'),
       LEAST('0.2','22','21')

In the following example, each LEAST compare three strings and returns the value with the lowest collation sequence:

SELECT LEAST('A','a',''),
       LEAST('a','aa','abc'),
       LEAST('#','0','7'),
       LEAST('##','00','77')

The following example compares two dates, treated as canonical numbers: the date of birth as a $HOROLOG integer, and the integer 58074 converted to a date. It returns the date of birth for each person born in the 20th century. Anyone born after December 31, 1999 is displayed with the default birth date of January 1, 2000:

SELECT Name,LEAST(DOB,TO_DATE(58074)) AS NewMillenium
FROM Sample.Person

See Also

FeedbackOpens in a new tab