LEAST

Overview

The LEAST() function returns the least or smallest value in a list of values. It needs at least one argument to work with, and if you mix different types, like a text and a number, it will return 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 as follows:

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 will be ignored.
- The result will be NULL if all the expressions evaluate to NULL.

Examples

Below are several examples of the LEAST() function:

Case #1: Basic Usage

Consider the following example:

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

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

 least 
-------
     3

Case #2: String Comparison

String comparison is also supported, as shown below:

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

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

 least 
-------
     a

Case #3: Handling 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

Case #4: 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

Case #5: Using Table Data

Suppose we have 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, you can use the following 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