LEAST() Function in Oracle

by

In Oracle Database, the LEAST() function returns the least of a list of one or more expressions.

Syntax

The syntax goes like this:

LEAST(expr [, expr ]...)

The first expr is used to determine the return type:

  • If the first expr is numeric, then Oracle 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 expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.

Example

Here’s an example:

SELECT LEAST('a', 'b', 'c')
FROM DUAL;

Result:

a

Here are some more:

SELECT 
    LEAST('A', 'a') AS "r1",
    LEAST('Cat', 'Dog', 'Dot') AS "r2",
    LEAST(1, 2, 3) AS "r3",
    LEAST(1, '2', 3) AS "r4",
    LEAST('Cat', '2001-12-31') AS "r5"
FROM DUAL;

Result:

   r1     r2    r3    r4            r5 
_____ ______ _____ _____ _____________ 
A     Cat        1     1 2001-12-31

Expressions

The arguments can include expressions like this:

SELECT LEAST(2 * 3, 1 * 3)
FROM DUAL;

Result:

3

Dates

Here’s a comparison of date strings:

SELECT LEAST(DATE '2020-01-01', DATE '2021-01-01')
FROM DUAL;

Result:

01-JAN-20

The date is returned in the date format of the current session. See How to Check the Date Format of the Current Session.

Null Values

If any argument is null, the result is null:

SET NULL 'null';
SELECT 
    LEAST(null, 2),
    LEAST(1, null)
FROM DUAL;

Result:

   LEAST(NULL,2)    LEAST(1,NULL) 
________________ ________________ 
null                         null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Invalid Argument Count

Calling LEAST() without any arguments results in an error:

SELECT LEAST()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"

You can also use GREATEST() to return the greatest of a list of one or more expressions.