The GREATEST() function in DuckDB is a versatile utility that returns the greatest value from a list of expressions. The function works across various data types and provides flexible comparison capabilities for data analysis tasks.
This article takes a look at DuckDB’s GREATEST() function, along with some simple examples.
Basic Syntax
GREATEST(expr1, expr2, ..., exprN)The function takes two or more expressions as arguments and returns the greatest 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 GREATEST() function with various data types.
Numbers
We can use GREATEST() to return the largest number from one or more numbers. Here’s an example that that gets the greatest integer from a list of integers:
SELECT GREATEST(10, 5, 20, 15) AS greatest_integer;Result:
+------------------+
| greatest_integer |
+------------------+
| 20 |
+------------------+
The result is the largest integer.
Here’s one that uses decimals:
SELECT GREATEST(10.5, 10.1, 10.9, 10.7) AS greatest_decimal;Result:
+------------------+
| greatest_decimal |
+------------------+
| 10.9 |
+------------------+
We can provide a combination of integers and decimals:
SELECT GREATEST(5, 7.5, 3.2, 9) AS greatest_mixed_numeric;Result:
+------------------------+
| greatest_mixed_numeric |
+------------------------+
| 9.0 |
+------------------------+
Although notice in this case a decimal was returned, even though that number was actually provided as an integer.
Strings
We can also use the GREATEST() function on strings.
Here’s a lexicographical comparison of strings:
SELECT GREATEST('apple', 'banana', 'cherry') AS greatest_string;Result:
+-----------------+
| greatest_string |
+-----------------+
| cherry |
+-----------------+
And here’s a case-sensitive string comparison:
SELECT GREATEST('Apple', 'apple', 'APPLE') AS greatest_case_sensitive;Result:
+-------------------------+
| greatest_case_sensitive |
+-------------------------+
| apple |
+-------------------------+
Lowercase letters have higher ASCII values than uppercase (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 GREATEST() to return the greatest (i.e. latest) date:
SELECT GREATEST(
DATE '1999-01-15',
DATE '2000-06-10',
DATE '2025-03-22'
) AS greatest_date;Result:
+---------------+
| greatest_date |
+---------------+
| 2025-03-22 |
+---------------+
We can also use TIMESTAMP values:
SELECT GREATEST(
TIMESTAMP '2023-01-15 10:30:00',
TIMESTAMP '2023-01-15 14:45:00',
TIMESTAMP '2023-01-15 08:15:00'
) AS greatest_timestamp;Result:
+---------------------+
| greatest_timestamp |
+---------------------+
| 2023-01-15 14:45:00 |
+---------------------+
Here’s an example that uses TIME values:
SELECT GREATEST(
TIME '00:30:00',
TIME '14:45:00',
TIME '07:10:00'
) AS greatest_time;Result:
+---------------+
| greatest_time |
+---------------+
| 14:45:00 |
+---------------+
Mixed Types
Mixing data types will usually result in an error:
SELECT GREATEST( TIME '10: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 GREATEST( DATE '2024-01-15', TIMESTAMP '2023-01-15 14:45:00') AS mixed;Result:
+---------------------+
| mixed |
+---------------------+
| 2024-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 GREATEST( TIME '10: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 GREATEST() to find the best quarter for each product:
SELECT
product_id,
GREATEST(sales_q1, sales_q2, sales_q3, sales_q4) AS best_quarter_sales
FROM sales;Result:
+------------+--------------------+
| product_id | best_quarter_sales |
+------------+--------------------+
| 1 | 180 |
| 2 | 95 |
| 3 | 215 |
| 4 | 310 |
| 5 | 305 |
+------------+--------------------+
We can use a CASE statement to display a different result, depending on the output of GREATEST():
SELECT
product_id,
GREATEST(sales_q1, sales_q2, sales_q3, sales_q4) AS best_quarter_sales,
CASE
WHEN GREATEST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q1 THEN 'Q1'
WHEN GREATEST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q2 THEN 'Q2'
WHEN GREATEST(sales_q1, sales_q2, sales_q3, sales_q4) = sales_q3 THEN 'Q3'
ELSE 'Q4'
END AS best_quarter
FROM sales;Result:
+------------+--------------------+--------------+
| product_id | best_quarter_sales | best_quarter |
+------------+--------------------+--------------+
| 1 | 180 | Q4 |
| 2 | 95 | Q3 |
| 3 | 215 | Q4 |
| 4 | 310 | Q3 |
| 5 | 305 | Q1 |
+------------+--------------------+--------------+
Null Handling
The GREATEST() function ignores NULL values:
SELECT GREATEST(10, NULL, 20, NULL) AS greatest_with_nulls;Result:
+---------------------+
| greatest_with_nulls |
+---------------------+
| 20 |
+---------------------+
But if all values are NULL, then NULL is returned:
.nullvalue 'null'
SELECT GREATEST(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 GREATEST() function without any arguments results in an error:
SELECT GREATEST();Output:
Binder Error: No function matches the given name and argument types 'greatest()'. You might need to add explicit type casts.
Candidate functions:
greatest(ANY, [ANY...]) -> ANY
LINE 1: SELECT GREATEST();
^
The error indicates that we need to provide at least one argument. Any other arguments are optional.
Passing One Argument
Calling the GREATEST() function with just one argument returns that argument:
SELECT GREATEST( 'Coffee' );Output:
+--------------------+
| greatest('Coffee') |
+--------------------+
| Coffee |
+--------------------+