LEAST

The LEAST function returns the smallest of the list of one or more expressions.

SQL syntax

LEAST (Expression [,...])

Parameters

LEAST has the parameter:

Parameter Description

Expression [,...]

List of one or more expressions that is evaluated to determine the smallest expression value. Operand or column can be numeric, character, or date. Each expression in the list must be from the same data type family.

Description

  • Each expression in the list must be from the same data type family or date subfamily. Data type families include numeric, character and date. The date family includes four subfamilies: date family, TIME family, TT_DATE family, and TT_TIMESTAMP family. As an example, do not specify a numeric expression and a character expression in the list of expressions. Similarly, do not specify a date expression and a TT_TIMESTAMP expression in the list of expressions.

  • If the first Expression is numeric, then TimesTen determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type.

  • If the first Expression is in the character family, and the operand or column is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If the operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the length of the largest expression. If one operand or column is of type CHAR or VARCHAR2 and the second operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.

  • TimesTen uses nonpadded comparison semantics for data types from the character family.

  • If the first expression is in the date family, the data type returned is the same data type as the first expression.

  • If any of the expressions is NULL, the result is NULL.

  • If the first Expression is in the character family, and the operand or column is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If the operand or column is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR. The returned data type length is equal to the largest of the expressions.

  • You can specify a maximum of 256 expressions.

Use the LEAST function to return the string with the smallest value: 

Command> SELECT LEAST ('SMALL','SMALLER','SMALLEST') FROM dual;
< SMALL >
1 row found.

Use the LEAST function to return the numeric expression with the smallest value. In this example, NUMBER is the data type with the highest numeric precedence, so arguments are implicitly converted to NUMBER before the comparison and the data type NUMBER is returned. First describe the table leastex to see the data types defined for columns col1 and col2. Then SELECT * from leastex to see the data. Then invoke the LEAST function. 

Command> DESCRIBE leastex;

Table SAMPLEUSER.LEASTEX:
  Columns:
    COL1                            NUMBER (2,1)
    COL2                            TT_BIGINT

1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM leastex;
< 1.1, 1 >
1 row found.
Command> SELECT LEAST (Col2,Col1) from leastex;
< 1 >
1 row found.

Use the DESCRIBE command to confirm that the data type returned is NUMBER: 

Command> DESCRIBE SELECT LEAST (Col2,Col1) FROM leastex;

Prepared Statement:
  Columns:
    EXP                             NUMBER

Use the LEAST function to return the DATE expression with the smallest value. DATE and TIMESTAMP are in the same date family. 

Command> SELECT LEAST (DATE '2007-09-17', 
           TIMESTAMP '2007-09-17:10:00:00') FROM dual;
< 2007-09-17 00:00:00 >
1 row found.

Attempt to use the LEAST function to return the smallest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions. 

Command> SELECT LEAST (TT_DATE '2007-09-17',
           TT_TIMESTAMP '2007-09-17:01:00:00') FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function LEAST
The command failed.

Use the LEAST function to return the TIME expression with the smallest value. 

Command> SELECT LEAST (TIME '13:59:59', TIME '13:59:58',
           TIME '14:00:00') FROM dual;
< 13:59:58 >
1 row found.