website logo
⌘K
šŸ Homepage
šŸ‘‹Introduction
Key Concepts & Architecture
šŸš€Run Oxla in 2 minutes
šŸƒā€ā™‚ļøRun Oxla on S3
šŸ›«Multi-node Deployment
šŸ‘Øā€šŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
šŸ†šDifferences Between Oxla vs. PostgreSQL
šŸ“ˆPublic Metrics
Docs powered by
Archbee
SQL Reference
...
SQL Functions
Numeric Functions

LEAST

15min

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:

Pgsql
|
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:

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


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

Pgsql
|
 least 
-------
     3


Case #2: String Comparison

String comparison is also supported, as shown below:

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


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

Pgsql
|
 least 
-------
     a


Case #3: Handling NULL Values

NULL values are ignored when determining the smallest value:

Pgsql
|
SELECT LEAST (5,null,9);


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

Pgsql
|
 least 
-------
     5


Case #4: Negative Numbers

Negative numbers can also be compared:

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


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

Pgsql
|
 least 
-------
    -8


Case #5: Using Table Data

Suppose we have a table named grades containing columns x, y, and z.

Pgsql
|
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:

Pgsql
|
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.

Pgsql
|
 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




Updated 31 Aug 2023
Did this page help you?
PREVIOUS
SIN
NEXT
GREATEST
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Syntax
Examples
Case #1: Basic Usage
Case #2: String Comparison
Case #3: Handling NULL Values
Case #4: Negative Numbers
Case #5: Using Table Data
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2023 Oxla