Cloud

LEAST

The LEAST function returns the least or smallest value in a list of values. It needs at least one argument to work with. If different types are mixed, like a text and a number, it returns an error.

For example, comparing the greatest value among 4, “two”, and 9 would result in an error.

Syntax

The syntax for the LEAST function is:

LEAST(value_1, [value_n])

Where:

  • value_1: Represents the first value.

  • value_n: Represents one or more additional values, separated by commas.

Info: * NULL values in the list are ignored. - The result will be NULL if all the expressions evaluate to NULL.

Examples

These examples show how to use the LEAST function:

Basic usage

Consider the example:

SELECT LEAST(3,5,8,9,10);

The query will return 3, the smallest value among the provided values.

 least
-------
     3

String comparison

String comparison is also supported:

SELECT LEAST('a','b','c','aa');

In this case, the result will be 'a', as it is the smallest string.

 least
-------
     a

Handle NULL values

NULL values are ignored when determining the smallest value:

SELECT LEAST (5,null,9);

The result will be the smallest non-NULL value, which is 5.

 least
-------
     5

Negative numbers

Negative numbers can also be compared:

SELECT LEAST (4,-4,-8,8);

This query will return -8, the smallest value among the provided numbers.

 least
-------
    -8

Use table data

Suppose there is a table named grades containing columns x, y, and z.

CREATE TABLE grades (
    name TEXT,
    x INT,
    y INT,
    z INT
);

INSERT INTO grades (name, x, y, z)
VALUES
    ('Jane', 50, 0, 70),
    ('Rio', 60, 30, 80),
    ('John', 60, 60, 86),
    ('Rose', 80, 90, 88),
    ('Gary', 100, 80, 90);

To find the smallest value among these columns, use the query:

SELECT *, LEAST(x, y, z) AS least_grade FROM grades;

This query will add a new column named least_grade to the result, displaying the smallest value among columns x, y, and z.

 name |  x  | y  | z  | least_grade
------+-----+----+----+-------------
 Jane |  50 |  0 | 70 |           0
 Rio  |  60 | 30 | 80 |          30
 John |  60 | 60 | 86 |          60
 Rose |  80 | 90 | 88 |          80
 Gary | 100 | 80 | 90 |          80