The LEAST function in Oracle SQL is the direct opposite of the GREATEST function. It looks at a list of values you provide and returns the one that is the smallest.
It's an incredibly useful function for comparisons within a single row. It works with numbers, text strings, and dates.
What is the LEAST Function in Oracle?
The LEAST(value1, value2, ...) function takes one or more expressions and returns the one that is considered the "least" or smallest.
- For Numbers: It returns the smallest number.
- For Text: It returns the string that comes first in alphabetical (binary) order.
- For Dates: It returns the earliest date.
Don't confuse LEAST with MIN()!
MIN()is an aggregate function that finds the single smallest value in a column across multiple rows.LEAST()is a scalar function that finds the smallest value from a list of arguments within the same row.
LEAST Function Syntax
The syntax for LEAST is:
LEAST(expr1, expr2, ...)
Let's break that down:
expr1, expr2, ...: A comma-separated list of one or more values or columns to compare. The function requires at least one argument.
Oracle determines the data type based on the first argument. It will then try to convert all other arguments to that data type before comparing them.
Oracle LEAST Function Examples
Here are two practical examples of how to use LEAST.
Example 1: Finding the Smallest Value from a List of Numbers with LEAST
This is the most common use. The function will look at all the numbers and return the smallest one. Note that Oracle will implicitly convert text strings (like '.000832') into numbers if the first argument is a number.
Query:
SELECT
LEAST(1, 2.1, .000832) AS "Smallest_Number"
FROM DUAL;
Result:
Smallest_Number
---------------
.000832
Example 2: Finding the "Least" String with LEAST
When used with text, LEAST returns the value that comes first in alphabetical order.
Query:
SELECT
LEAST('HARRY', 'HARRIOT', 'HAROLD') AS "First_Alphabetically"
FROM DUAL;
Result:
First_Alphabetically
--------------------
HAROLD
