In DuckDB, the LEAST() function returns the smallest value from a list of expressions. The function works across various data types and provides flexible comparison capabilities for data analysis tasks.
In this article, we’ll explore DuckDB’s LEAST() function with some simple examples.
Basic Syntax
First, let’s look at the syntax:
LEAST(expr1, expr2, ..., exprN)The function takes two or more expressions as arguments and returns the smallest among them. The expressions can be any data type, as long as they’re not mixed. For example, we can pass two numbers or two dates, but not a date and a number.
Simple Examples with Different Data Types
Below are examples to demonstrate the LEAST() function with various data types.
Numbers
We can use LEAST() to return the smallest number from one or more numbers. Here’s an example that that gets the smallest integer from a list of integers:
SELECT LEAST(10, 5, 20, 15) AS smallest_integer;Result:
+------------------+
| smallest_integer |
+------------------+
| 5 |
+------------------+
The result is the smallest integer.
The following example uses decimals:
SELECT LEAST(10.5, 10.1, 10.9, 10.7) AS smallest_decimal;Result:
+------------------+
| smallest_decimal |
+------------------+
| 10.1 |
+------------------+
We can provide a combination of integers and decimals:
SELECT LEAST(5.1, 7.5, 3, 9) AS smallest_mixed_numeric;Result:
+------------------------+
| smallest_mixed_numeric |
+------------------------+
| 3.0 |
+------------------------+
Notice that a decimal was returned, even though that number was actually provided as an integer.
Strings
We can also use the LEAST() function on strings. When we do this, LEAST() selects the smallest value using lexicographical ordering.
Here’s a lexicographical comparison of strings:
SELECT LEAST('apple', 'banana', 'cherry') AS smallest_lex_string;Result:
+---------------------+
| smallest_lex_string |
+---------------------+
| apple |
+---------------------+
And here’s a case-sensitive string comparison:
SELECT LEAST('Apple', 'apple', 'APPLE') AS smallest_case_sensitive;Result:
+-------------------------+
| smallest_case_sensitive |
+-------------------------+
| APPLE |
+-------------------------+
Uppercase letters have lower ASCII values than lowercase (lowercase letters range from 97 to 122 on the ASCII table, whereas uppercase letters range from 65 to 900).
Date & Time Values
We can use LEAST() to return the earliest date:
SELECT LEAST(
DATE '1999-01-15',
DATE '2000-06-10',
DATE '2025-03-22'
) AS earliest_date;Result:
+---------------+
| earliest_date |
+---------------+
| 1999-01-15 |
+---------------+
We can also use TIMESTAMP values:
SELECT LEAST(
TIMESTAMP '2023-01-15 10:30:00',
TIMESTAMP '2023-01-15 14:45:00',
TIMESTAMP '2023-01-15 08:15:00'
) AS earliest_timestamp;Result:
+---------------------+
| earliest_timestamp |
+---------------------+
| 2023-01-15 08:15:00 |
+---------------------+
Here’s an example that uses TIME values:
SELECT LEAST(
TIME '00:30:00',
TIME '14:45:00',
TIME '07:10:00'
) AS earliest_time;Result:
+---------------+
| earliest_time |
+---------------+
| 00:30:00 |
+---------------+
Mixed Types
Mixing data types will usually result in an error:
SELECT LEAST( TIME '18:30:00', TIMESTAMP '2023-01-15 14:45:00');Result:
Binder Error: Cannot combine types of TIME and TIMESTAMP - an explicit cast is required
However, there are cases where we don’t get an error. For example:
SELECT LEAST( DATE '2000-01-15', TIMESTAMP '2023-01-15 14:45:00') AS mixed;Result:
+---------------------+
| mixed |
+---------------------+
| 2000-01-15 00:00:00 |
+---------------------+
In this case, the DATE value was converted to a TIMESTAMP value, and the time portion was set to 00:00:00.
If we really wanted to compare the TIME and TIMESTAMP values without getting an error, we’d need to explicitly cast one of the values to match the other value:
SELECT LEAST( TIME '18:30:00', CAST(TIMESTAMP '2023-01-15 14:45:00' AS TIME)) AS mixed_cast;Result:
+------------+
| mixed_cast |
+------------+
| 14:45:00 |
+------------+
Some Database Examples
Let’s create a database table and populate it with data:
-- Create a sample table
CREATE TABLE sales (
product_id INT,
sales_q1 INT,
sales_q2 INT,
sales_q3 INT,
sales_q4 INT
);
-- Insert sample data
INSERT INTO sales VALUES
(1, 120, 150, 110, 180),
(2, 90, 85, 95, 88),
(3, 200, 210, 190, 215),
(4, 105, 73, 310, 117),
(5, 305, 97, 110, 151);Now let’s use LEAST() to find the worst quarter for each product:
SELECT
product_id,
LEAST(sales_q1, sales_q2, sales_q3, sales_q4) AS worst_quarter_sales
FROM sales;Result:
+------------+---------------------+
| product_id | worst_quarter_sales |
+------------+---------------------+
| 1 | 110 |
| 2 | 85 |
| 3 | 190 |
| 4 | 73 |
| 5 | 97 |
+------------+---------------------+
We can use a CASE statement to display a different result, depending on the output of the LEAST() function:
SELECT
product_id,
LEAST(sales_q1, sales_q2, sales_q3, sales_q4) AS worst_quarter_sales,
CASE
WHEN LEAST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q1 THEN 'Q1'
WHEN LEAST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q2 THEN 'Q2'
WHEN LEAST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q3 THEN 'Q3'
ELSE 'Q4'
END AS worst_quarter
FROM sales;Result:
+------------+---------------------+---------------+
| product_id | worst_quarter_sales | worst_quarter |
+------------+---------------------+---------------+
| 1 | 110 | Q3 |
| 2 | 85 | Q2 |
| 3 | 190 | Q3 |
| 4 | 73 | Q2 |
| 5 | 97 | Q2 |
+------------+---------------------+---------------+
Null Handling
The LEAST() function ignores NULL values:
SELECT LEAST(10, NULL, 20, NULL) AS least_with_nulls;Result:
+------------------+
| least_with_nulls |
+------------------+
| 10 |
+------------------+
But if all values are NULL, then NULL is returned:
.nullvalue 'null'
SELECT LEAST(NULL, NULL, NULL) AS all_nulls;Result:
+-----------+
| all_nulls |
+-----------+
| null |
+-----------+
In this example I used .nullvalue 'null' in order to display the string null whenever a NULL value is returned. By default, the DuckDB CLI returns an empty string for NULL values. We can therefore use .nullvalue 'null' in order to distinguish between NULL values and an actual empty string in the data.
No Arguments
Calling the LEAST() function without any arguments results in an error:
SELECT LEAST();Output:
Binder Error: No function matches the given name and argument types 'least()'. You might need to add explicit type casts.
Candidate functions:
least(ANY, [ANY...]) -> ANY
LINE 1: SELECT LEAST();
^
The error indicates that we need to provide at least one argument. Any other arguments are optional.
Passing One Argument
Passing just one argument returns that argument:
SELECT LEAST( 10 );Output:
+-----------+
| least(10) |
+-----------+
| 10 |
+-----------+